I read that CONSISTENCY imposes table locking even for read operations which means that I will continue using CONCURRENCY mode. Additionally, I will not run both update processes at the same time to avoid the problem I have been having.
On Sat, Dec 19, 2015 at 11:01 PM, Caroline Beltran < caroline.d.belt...@gmail.com> wrote: > 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' a...@meta.com.au > [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 >> >> >> >> >> > >
