Physical and logical logging
As each of the various CPU processes are running, the database engine keeps
track of a number of operations. When data is retrieved from disk, the data image
is placed in a physical log buffer. Change process information is placed in the
logical log. When image data is affected by SQL processing, the physical image
is modified periodically and written back to disk.
During this process the physical log image and logical log information are also
written into log records. Depending on the commit protocols and isolation levels,
this information is available to the DBA through recovery operations in the event
that the information is needed. The DBA can also use automatic recovery
features that are built in to the engine can if the engine abruptly goes offline due
to a power failure or crash.
Commit protocols
If transactions are made against a database that uses unbuffered logging, the
records in the logical-log buffer are guaranteed to be written to disk during
commit processing. When control returns to the application after the COMMIT
statement (and before the PREPARE statement for distributed transactions), the
logical-log records are on the disk. The database server flushes the records as
soon as any transaction in the buffer is committed (that is, a commit record is
written to the logical-log buffer).
If transactions are made against a database that uses buffered logging, the
records are held (buffered) in the logical-log buffer for as long as possible. They
are not flushed from the logical-log buffer in shared memory to the logical log on
disk until one of the following situations occurs:
The buffer is full.
A commit on a database with unbuffered logging flushes the buffer.
A checkpoint occurs.
The connection is closed.
If you use buffered logging and a failure occurs, you cannot expect the database
server to recover the transactions that were in the logical-log buffer when the
failure occurred. Thus, you might lose some committed transactions. In return for
this risk of using buffered logging, performance during alterations improves
slightly.