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' [email protected]
[firebird-support] <[email protected]> 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
>
>
>
>
>
