Introduction

On Tuesday, we discussed four kinds of anomalies that are common in a concurrent DB setting: phantom reads, nonrepeatable reads, lost updates, and dirty reads.
We then discussed DB2 isolation levels.
We then discussed locking in detail in DB2.
Today, we are going to finish up our discussion of concurrency in DB2 and then start talking about logging and recovery.

Parameters that affect locking

Here is a summary of the main "tuning knobs" for locking.
Commit releases the strong exclusive locks.
When creating a TABLESPACE, you can specify the following parameters: LOCKSIZE (page or row) and LOCKMAX. When the number of locks held exceeds LOCKMAX, the low level lock will be escalated to be high level gross lock.
On static bind or dynamic prepare, different isolation level affects the duration of locks. Locks can be avoided on ISO (UR) and on ISO(CS) with CURRENTDATA(NO). Lock avoidance is also possible with ISO(RS) for predicate evaluation.
Finally when you write your application, make sure you access data in the same order as other application to avoid deadlock.

DB2 Serialization Mechanisms

DB2 has several serialization mechanisms...

Transaction locking -- we've already covered this.
Restricted states -- STOP, START UT, CHKP, UTUT, UTRO, UTRW. Restricted states can be set on DB2 objects by a DB2 utility or a DB2 command. For example, the administrator can STOP a database to work on repair of the database.
Latching -- this is like a system level lock used to maintain a pages integrity. We'll discuss this more in a moment.
Claims and drains -- we'll also discuss these in a moment.

Latches

Latches on data are always at the page level.
When an operation is done on a row in a table, the row may shrink or grow in size.
If it shrinks, the extra area becomes a "hole" (is chained into a chain of available space on the page). If it needs to grow, other rows on the page may have to be moved around so we can coalesce holes to make enough contiguous space on the page to accommodate the now larger row.
Either of these operations also affects the count of free bytes on the page.
It wouldn't be good for a reader to look at and try to retrieve data from a page while these physical changes are taking place, even if the interest is in a row other than the one we are updating.
A latch is a lock on the page in memory while it is being manipulated, so that other operations don't affect the page while it is being manipulated.
As soon as the modification is done the latch is released.
Latches are needed because it is possible for a user to have row level rather than page level locks.
Latches are DB2 internal locks. Latch acquisition and release does not use the IRLM service. It is handled by the DB2 Buffer Manager code.

Claim and Drain

Claim and drain is a complementary serialization mechanism in DB2 that is present in addition to the more familiar locking mechanism.
As with transaction locks, claims and drains can time out while waiting for a resource.

Claims

DB2 uses a claim to register that a resource is being accessed.
A process can stake a claim on a resource such as simple table spaces, segmented table spaces, a single data partition of a partitioned table space, a non-partitioned index space, or a single index partition of a partitioned index
A claim is acquired when a resource is first accessed. Claims prevent drains from acquiring a resource.
Every claim has a claim class, which is based on the requested access type. A reader of ISO(RR) gets a RR claim. A non-RR reader gets a CS claim. A writer gets a write claim.

Drains

A drain is a process which takes over access to an object by preventing new claims and waiting for existing claims to be released.
For example: A utility can drain a partition when applications are accessing it. The drain quiesces the applications by allowing each one to reach a commit point, but preventing any of them, or any other applications, from making a new claim. When no more claims exist, the process that drains (the drainer) controls access to the drained object.
Drains can be requested by DB2 commands and utilities.A drainer does not always need complete control. It could drain the following combination of claim classes: Only the write claim class, only the repeatable read claim class, all claim classes.
For example, the CHECK INDEX utility needs to drain only writers from an index space and its associated table space. RECOVER, however, must drain all claim classes from its table space. The REORG utility can drain either writers (with DRAIN WRITERS) or all claim classes (with DRAIN ALL).

Logging and Recovery

That completes our discussion of DB2 Concurrency Control.
We are next going to look at how logging and recovery work in DB2.
In order to ensure that the DB2 product is robust, we need mechanisms to ensure that if a failure occurs, the database can be brought back to a consistent state.
We also want to ensure that we don't lose any information that has been committed.
Finally, we want the previous two things to hold and we want to minimize the time and cost of recovery.
How this is done will be the topic of the rest of today's lecture.

DB2 Memory Structure and Deferred Write

Before we jump into recovery it is useful to refresh our understanding of how things are stored in DB2.
We know that DB2 is running on a hierarchical memory structure, consisting of memory and buffer pools (volatile media) as well as DASD, tapes and other slower mass medias (non-volatile).
To avoid I/O overhead, all the changes to the DB are made in memory. The updated data is cached in the data buffers, waiting for an appropriate time to be written out.
As part of its data is in the volatile storage, like memory or buffers, and part of the data is in non-volatile memories like DASD, tapes, etc., failures to volatile media may corrupt your data, and cause problems to DB2.
Today, we are going to look at how we fix and prevent those problems.

Risks of System Failure

Power loss or software errors sometimes lead to system failure.
System failure causes loss of data in the volatile memory.
Updates that are made by committed transactions are cached in buffers and not yet written to disk.
What we'd like is if committed data has not yet reached disk, we could redo these lost changes.
We'd also like if data that was not committed but is already written to disk that we can undo aborted changes
These are the two main operation that we need logging to support.

Logging as a Remedy for System Failure

A log is a set of DASD files that record all the changes made to a database
Two reasons for using logs are that logs provide a succinct way to record what has changed on a database and more information can be obtained from the log than by just looking at the final state of the data on DASD.
The first point can be seen by realizing that the minimum size of any operation that is sent to disk is a page. On the other hand, a log record can record precisely what has changed in a given row of a given pages. Many log records can fit within a single log record page, and they could be recording the changes that have occurred on many table pages.
The second point can be seen from the fact that in the time period between consecutive writes of a table page to disk, many changes might have been made to that page. All of the changes and when they occurred will be recorded in the log.

Master Copy of Data

The log is fundamental to DB2's ability to recover data after media, system, or subsystem failures. It is regarded as the master copy of data in a database.
An update operation is completed only when the log about it reaches the disk, and DB2 must ensure that the logs are taken down to DASD as soon as feasible, so as to be resilient from failures.
Part of writing the log data as soon as possible is that multiple threads all use the same log and a log buffer can be written as soon as it is full (and sometimes even before it is full) by an asynchronous thread.
Therefore, by the time a transaction is about to complete, its log records may have already been written to DASD, especially in a busy system.

Categories of Log Information

DB2 makes use of several different kinds of log records:

Details about every update to DB2 units of recovery are stored in (UR) log records. These can be used to revert or regenerate transaction data.
System state is written in the checkpoint records that can be used to speed-up recovery by reducing the number of log records that need to be examined during recovery
Other system information is also recorded in log records such as dataset status information (open, close, etc)

Logging Process Overview

A diagram showing active and archive logs

The DB2 log is mapped onto data sets. Each DB2 system has a predefined fixed set of active log data sets on disk.
Log records are first written by DB2 into a log buffer. They are subsequently written from there onto the active log data sets.
As an active log data set fills up, DB2 moves onto the next active log data set.
When all active log data sets have been filled, DB2 wraps around to the first active log data set and uses it again.
In order not to lose log records that may be required for a backout or recovery, the active log data sets are automatically offloaded as they are filled up.
They are offloaded by DB2 to archive log data sets, which may be on a tape (many years ago) but now frequently reside on DASD.
Only those archive logs that are still needed for backout or recovery operations need to be kept.
The individual log data sets, both active and archive, are recorded in the Bootstrap Data Set (BSDS).

Unit of Recovery Logs

In the event of a failure, logs are used in the following way to get the database back into a consistent state:

Uncommitted transactions are backed out. This involves undoing updates by uncommitted transactions
Then committed transactions have their updates written. That is, we redo lost changes by committed transactions

Unit of recovery records are either undo/redo records, or control records of the form begin UR, end UR, ABORT, etc.

Recall a unit of recovery is a DB2 mechanism to track the progress of a transaction as it updates the database

It begins with the first change to the data after the beginning of the job, or after the last point of consistency

It ends at a later point of consistency

Undo Logs

Undo log records have the format: <T, X, v>.
The meaning of this is that transaction T has changed database element X, and its former value was v
To recover using such a record one restores the old value of a DB2 element that had changed.
The effect of this is to cancel the updates by an uncommitted transactions in the backout process.

Redo Logs

Redo log records have the format: <T, X, v>.
The meaning of this is that transaction T has changed database element X, and its new value v
To recover using such a record one applies the new value v to X during recovery, regardless of whether the new value has already been applied
The effect of this is to repeat the update in the recovery process, thus, rebuilding any lost content

Recovery with Logs

So the complete process of recovery might be viewed so far as moving back through the log applying undo records of incomplete or aborted transactions until one gets to the last point of consistency.
Then one moves forward through the logs from this point applying redo records.
We still have some details to work out though...
We need a mechanism of knowing when transactions begin and end in the log.
We need to be able to see who aborted, and who was not committed, and who was committed.
We need to be able to find the last point of consistency.

Unit of Recovery Control Records

UR records will allow us to know when a transaction began or ended. They come in the following flavors:

START T -- This indicates transaction T has begun
COMMIT T -- This indicates transaction T has completed successfully and the log was externalized
ABORT T -- This indicates transaction T is canceled prior changes made by T are rolled back by the transaction manager

Compensation Logs

Suppose the system again crashes while we are recovering from a previous crash.
We are still okay! Every database change activity is recorded into the log for recovery purpose, including the recovery activity itself.
We can simply refer to the logs to tell us what we've done during the last recovery process.
If the work is rolled back, the undo/redo record is used to remove the change. At the same time that the change is removed, a new redo/undo record is created that contains information, called compensation information, which is used if necessary to reverse the change.

Checkpoints

We know return to the questions: How far back in the logs do we need to go during the undo phase? Which transactions need to be redone?
In principle, the first question could be answered by searching the entire log from the very beginning, identifying those transactions for which there is a BEGIN UR record but no termination (COMMIT or ROLLBACK) records. Of course such a search could be very time consuming.
For the second question, since the writing of data is performed asynchronously with the UR progress, we need additional information to tell us which URs are completed but didn't write out at the point of failure.
These problems are both solved more efficiently by the use of "checkpointing".
A checkpoint occurs at certain pre-specified intervals (say every five minutes, or whenever some given amount of log data has been written).
During a checkpoint, the system logs down information about the ongoing URs as well as on-going I/Os. This information will be checked at restart; and will thus help the Recovery Manager to identify the URs that need to be recovered.

Finish Concurrency, Logging and Recovery

Outline