Subject Re: [firebird-support] Re: Problem with application that creates hundreds of transactions per minute
Author Caroline Beltran
Post date 2015-12-20T05:01:30Z
Alan, I think you are right.  I backed up then restored my database.  Next I installed SuperServer and everything was going very well.  Here are my stats:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              22516
        Page size               4096
        ODS version             11.2
        Oldest transaction      22474
        Oldest active           22475
        Oldest snapshot         22475
        Next transaction        22476
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      34
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 20:00:46
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

Then I started a secondary process that also updates the database and within a few minutes, my stats did not look as they did before:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              28442
        Page size               4096
        ODS version             11.2
        Oldest transaction      25127
        Oldest active           25128
        Oldest snapshot         25128
        Next transaction        28396
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      40
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 20:00:46
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

This is when I noticed that things locked up.  I stopped all of the update processes and waited a minute or two and things went back to normal and everything was accessible.

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              29520
        Page size               4096
        ODS version             11.2
        Oldest transaction      29446
        Oldest active           29447
        Oldest snapshot         29447
        Next transaction        29448
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      66
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 20:00:46
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*


All of my non-mutable operations (e.g. SELECTs) are read access transactions with isolation mode readcommitted.

All of my mutable operations (INSERTs and UPDATEs) are write access transactions with isolation mode concurrency with wait lock resolution.


The weird thing about this is that I can run the primary process by itself or the secondary process by itself and there are no issues whatsoever.  But if I run both concurrently, that is when this issue happens.

Process 1 SELECTs from table1 and table2 to determine the INSERTs and UPDATEs to table1, table2, and table3.

Process 2 SELECTs from table1 and UPDATES table1

I am now thinking that when both processes run concurrently transactions may be overlapping one another.  For example, a transaction to edit record1 may begin while another transaction begins to edit other fields belonging to the same record.

I may try changing my mutable operations from CONCURRENCY to CONSISTENCY to see if this helps.

On Sat, Dec 19, 2015 at 8:32 PM, 'Alan McDonald' alan@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 

I can reproduce this 'stalling' behavior.  At the moment, the application is importing records without delay.  CPU consumption remains very low.

To stall FB, I will now run a query that will scan several thousands of records that have just been INSERTed and UPDATEd.  Before I run this query, here are my stats:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              65205
        Page size               4096
        ODS version             11.2
        Oldest transaction      65091
        Oldest active           65092
        Oldest snapshot         65092
        Next transaction        65193
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      64
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 14:27:42
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

The query normally takes less a few  seconds and is now stalled (7:18PM).  My application's import process has also stalled and the web application is not serving pages.  FB CPU consumption is at approx 1-2% and Read and Write I/O is very active, there is plenty of disk activity going on.

I was using FB 2.5.4 and upgraded to 2.5.5 Superserver but the problem is the same.  Running on Windows 2012.

The FB Server is definitely doing something, 35 minutes later, here are the stats:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              66143
        Page size               4096
        ODS version             11.2
        Oldest transaction      65091
        Oldest active           65092
        Oldest snapshot         65092
        Next transaction        66131
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      84
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 14:27:42
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

Unfortunately, this is going painfully slow and I will shut down FB 2.5.5, backup and restore the database, and try with SuperClassic instead of SuperServer.

 

Sounds more like a transaction management issue to me.

You need to tell us what component set you are using for connection and the transaction settings they use.

Your insert transactions should be committing, not commit-retaining or whatever your component set describes this as.

You should do the inset in batches and hard commit more frequently.

Alan