How DB2 Manages Locking

Anyone accustomed to application programming when access to a database is required understands the potential for concurrency problems. When one application program tries to read data that is in the process of being changed by another, the DBMS must forbid access until the modification is complete to ensure data integrity. Most DBMS products, DB2 included, use a locking mechanism for all data items being changed. Therefore, when one task is updating data on a page, another task cannot access data (read or update) on that same page until the data modification is complete and committed.

When multiple users can access and update the same data at the same time, a locking mechanism is required. This mechanism must be capable of differentiating between stable data and uncertain data. Stable data has been successfully committed and is not involved in an update in a current unit of work. Uncertain data is currently involved in an operation that could modify its contents. Consider the example in Listing 23.1.

Listing 23.1. A Typical Processing Scenario

 Program #1                  Timeline    Program #2 .                           T1          . .                                       . .                                       . .                                       . SQL statement               T2          . accessing EMPNO '000010'                . .                                       . .                                       . SQL statement               T3          . updating '000010'                       . .                                       . .                                       . .                           T4          SQL statement                                         accessing EMPNO '000010' .                                       . .                                       . Commit                      T5          . .                                       . .                                       . .                                       . .                           T6          SQL statement updating '000010' .                                       . .                                       . .                                       . .                           T7          Commit 

If program #1 updates a piece of data on page 1, you must ensure that program #2 cannot access the data until program #1 commits the unit of work. Otherwise, a loss of integrity could result. Without a locking mechanism, the following sequence of events would be possible:

1. Program #1 retrieves a row from DSN8810.EMP for EMPNO '000010' .

2. Program #1 issues an update statement to change that employee's salary to 55000 .

3. Program #2 retrieves the DSN8810.EMP row for EMPNO '000010' . Because the change was not committed, the old value for the salary, 52750 , is retrieved.

4. Program #1 commits the change, causing the salary to be 55000 .

5. Program #2 changes a value in a different column and commits the change.

6. The value for salary is now back to 52750 , negating the change made by program #1.

The DBMS avoids this situation by using a locking mechanism. DB2 supports locking at four levels, or granularities : table space-, table-, page-, and row-level locking. DB2 also provides LOB locking for large objects (BLOBs, CLOBs, and DBCLOBs). More precisely, DB2 locks are enacted on data as shown in Figure 23.1.

Figure 23.1. The DB2 locking hierarchy.

These two charts are hierarchical. Locks can be taken at any level in the locking hierarchy without taking a lock at the lower level. However, locks cannot be taken at the lower levels without a compatible higher-level lock also being taken. For example, you can take a table space lock without taking any other lock, but you cannot take a page lock without first securing a table space-level lock (and a table lock as well if the page is part of a table in a segmented table space).

Additionally, as illustrated in the diagrams in Figure 23.1, a page lock does not have to be taken before a row lock is taken. Your locking strategy requires an "either/or" type of choice by table space: either row locking or page locking. An in-depth discussion on the merits of both follows later in this chapter. Both page locks and row locks escalate to a table level and then to a table space level for segmented tables or straight to a table space level for simple or partitioned table spaces. A table or table space cannot have both page locks and row locks held against it at the same time.

Many modes of locking are supported by DB2, but they can be divided into two types:

• Locks to enable the reading of data

• Locks to enable the updating of data

But this overview is quite simplistic; DB2 uses varieties of these two types to indicate the type of locking required. They are covered in more depth later in this chapter.