The cornerstone of the crash recovery

How many times have you encountered the error message "sql0946c The transaction log for the"?

When trying to solve the problem, do you stop to think about why the transaction log and the transaction logging service are intended?

Without transactions, multiple users and applications that interact with a database at the same time will inevitably destroy the data. Without transaction logging, some library recovery methods in the DB2 UDB do not exist.

If you haven't fully understood these concepts, don't worry. I'll explain what the transaction is and the mechanism behind the transaction log record. Then, I'll show you how to use the information stored in the database transaction log file to bring the database back to a consistent, usable state after a system crash or a program failure.

But you can also do more with these important logs. In future columns, I'll show you how to use the transaction log file to reproduce the operation to restore the database exactly to the state it was in at a given point in time.

Transaction

A transaction (also known as a unit of work) is a sequence of one or more SQL operations, grouped together into a single unit and typically within an application process. The unit is often referred to as "atomic" because it is not divided-all of its work is either executed or all is not performed. A given transaction can perform any number of SQL operations (from one to thousands of, depending on the definition of "one-step" in the business logic).

The start and end of a transaction define the point of data consistency in the database; either the results of all operations performed in the transaction are applied to the database and made permanent (committed), or both are revoked (rolled back) and the database is returned to the state before the transaction was started.

A transaction is launched the first time an SQL statement is executed after a connection is established to the database, or immediately after an existing transaction terminates. Once started, the transaction can be terminated implicitly by using a feature named Atomic commit. With an atomic commit, each executable SQL statement is treated as a transaction. If the statement succeeds, any modifications it makes will be applied to the database, but if the statement fails, the modification is discarded.

You can also explicitly terminate a transaction by executing a COMMIT or ROLLBACK SQL statement.

The basic syntax for these statements is:

COMMIT <WORK>
和
ROLLBACK <WORK>

When a COMMIT terminates a transaction, all modifications made to the database by the transaction from the beginning are permanent. With ROLLBACK, all modifications will be revoked.

Uncommitted modifications made by the firm are inaccessible to other users and applications unless those users and applications are using uncommitted read (UR) isolation. However, once the changes are committed, they are accessible to all other users and applications and can only be deleted by executing a new SQL statement in the new transaction.

Transaction log records

When you INSERT into a base table, you first create a record in the buffer pool that is associated with the table space where the data for the table is stored. Each time a record is updated or deleted, the page containing the record is retrieved from the storage and copied to the appropriate buffer pool, which is then modified by Update/delete. Once this modification is made, a record is written to the log buffer that reflects the action, and the log buffer is another specified store in memory. (The true storage size reserved for the log buffers is controlled by the Logbufsiz database configuration parameters.) If you execute the INSERT, a record is written that contains the new row data value. When a deletion occurs, a record is written that contains the original value of the row. If you execute UPDATE, a record is written that contains the original and new values of the row. (In most cases, a log record is generated for the update operation by executing EXCLUSIVE OR on the original value with the updated value of the row.) Finally, the corresponding COMMIT or ROLLBACK record is written to the log buffer when the transaction executing the INSERT, UPDATE, or DELETE terminates.

Whenever the buffer pool I/O page cleaner is activated, the log buffer itself is full, or when a transaction is committed or rolled back, all the records stored in the log buffer are immediately written to one or more transaction log files stored on the disk. If a system failure occurs, constant flushing of the log buffers minimizes the number of log records that may be lost. Once all the log records associated with a particular transaction (including the corresponding COMMIT or ROLLBACK record) are successfully materialized (externalize) to one or more log files, the results of the transaction itself are copied to the appropriate tablespace container for permanent storage. (The modified data pages themselves remain in memory and can be accessed quickly when necessary; they will eventually be overwritten.) This process is called pre-write logging (Write-ahead logging), ensuring that changes made to the data are always materialized as log files before being logged to the database. (See Figure 1)

Because multiple transactions can use a database at any time, a log file may contain logging that belongs to several different transactions. To track which log records belong to which transaction, assign a special transaction ID to each log record and bind it to the transaction that created it. By using the transaction ID, the log records associated with a particular transaction can be written to one or more log files at any time without affecting data consistency-eventually, a COMMIT or ROLLBACK record for the operation that terminates the transaction is also logged.