IDS Forum

Re: COMMIT WORK, LRU's and Checkpoints.

Posted By: Art Kagel
Date: Thursday, 13 August 2015, at 11:38 p.m.

In Response To: COMMIT WORK, LRU's and Checkpoints. (RAY BURNS)

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

Messages In This Thread

• COMMIT WORK, LRU's and Checkpoints.
  RAY BURNS -- Thursday, 13 August 2015, at 10:38 p.m.
  • Re: COMMIT WORK, LRU's and Checkpoints.
    Art Kagel -- Thursday, 13 August 2015, at 11:38 p.m.
    • Re: COMMIT WORK, LRU's and Checkpoints.
      RAY BURNS -- Friday, 14 August 2015, at 12:44 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.