When an agent updates a page, the database manager uses a protocol
to minimize the I/O that is required by the transaction and to ensure recoverability.
This protocol includes the following steps:
- The page that is to be updated is pinned and latched with an exclusive
lock. A log record is written to the log buffer, describing how to undo and
redo the change. As part of this action, a log sequence number (LSN) is obtained
and stored in the header of the page that is being updated.
- The update is applied to the page.
- The page is unlatched. The page is considered to be "dirty", because
changes to the page have not yet been written to disk.
- The log buffer is updated. Both data in the log buffer and the dirty data
page are written to disk.
For better performance, these I/O operations are delayed until there is
a lull in system load, or until they are necessary to ensure
recoverability or to limit recovery time. More specifically, a dirty page
is written to disk when:
- Another agent chooses it as a victim
- A page cleaner works on the page. This can occur when:
- Another agent chooses the page as a victim
- The chngpgs_thresh database configuration parameter
value is exceeded, causing asynchronous page cleaners to wake up and write
changed pages to disk. If proactive page cleaning is enabled for the database,
this value is irrelevant and does not trigger page cleaning.
- The softmax database configuration parameter value
is exceeded, causing asynchronous page cleaners to wake up and write changed
pages to disk. If proactive page cleaning is enabled for the database, and
the number of page cleaners has been properly configured for the database,
this value should never be exceeded.
- The number of clean pages drops too low. Page cleaners only react to this
condition under proactive page cleaning.
- A dirty page currently contributes to, or is expected to contribute to
an LSN gap condition. Page cleaners only react to this condition under proactive
page cleaning.
- The page is part of a table that was defined with the NOT LOGGED INITIALLY
clause, and the update is followed by a COMMIT statement. When the COMMIT
statement executes, all changed pages are flushed to disk to ensure recoverability.