COMMIT WORK, LRU's and Checkpoints.

I wonder if someone could explain what happens when a commit work occurs in terms of the buffer cache? The SQL Manual for COMMIT WORK has the following sentence: "The database server takes the required steps to make sure that all modifications that the transaction makes are completed correctly and saved to disk." I know that pages are written to disk from the cache mainly by LRU Cleaners, Checkpoints and foreground writes. What I am unsure is what is the effect of a commit work SQL statement? Does the commit work simply write to the cache and the logical log (and leave the cache write to something else), or does it cause some sort of page flush as well? (I don't believe this is a FGWRITE because I can see a very low level of FGWRITES in the db). If this is so, is there any way of monitoring these writes? I have a database where every table is logged and every update is within a transaction. What is the effect on the LRU / Buffer Pool / Checkpoints ? Ray

Ray: When a COMMIT record is written to the logical log buffer, if the database affected by the transaction being committed is UNBUFFERED then the logical log buffer is flushed to disk. ONce the COMMIT record has been written to disk the transaction's changes are permanent since after a crash the engine can roll forward these logical log records to recreate the the transaction even if the affected dirty buffers have not been written out to disk yet (see below). If the affected database(s) are all BUFFERED log then the logical log buffer is not written to disk until it fills. So, with BUFFERED logging there is a small window of risk that this COMMIT in the logical log buffer will never be written in the event of a crash and that a record that the client thinks is committed will be rolled back when the server recovers. Dirty buffers in the buffer cache are not written out to disk except by the CLEANERS by foreground, LRU, or checkpoint (ie chunk) writes. In v11.50 and later, when a non-blocking checkpoint occurs, the dirty buffers are flushed after the checkpoint. If the server crashes before those writes complete, the server will roll back to the previous completed checkpoint and roll logical log buffers forward from that point instead. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 13, 2015 at 10:38 PM, RAY BURNS <ray.burns@velocityglobal.co.nz> wrote: > I wonder if someone could explain what happens when a commit work occurs in > terms of the buffer cache? The SQL Manual for COMMIT WORK has the following > sentence: > > "The database server takes the required steps to make sure that all > modifications that the transaction makes are completed correctly and saved > to > disk." > > I know that pages are written to disk from the cache mainly by LRU > Cleaners, > Checkpoints and foreground writes. What I am unsure is what is the effect > of a > commit work SQL statement? > > Does the commit work simply write to the cache and the logical log (and > leave > the cache write to something else), or does it cause some sort of page > flush > as well? (I don't believe this is a FGWRITE because I can see a very low > level > of FGWRITES in the db). If this is so, is there any way of monitoring these > writes? > > I have a database where every table is logged and every update is within a > transaction. What is the effect on the LRU / Buffer Pool / Checkpoints ? > > Ray > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0111c0168abe58051d3d2d73

Many thanks.