DB2 - Locks

---

DB2 automatically guarantees the integrity of data by enforcing several locking strategies. These strategies permit multiple users from multiple environments to access and modify data concurrently. DB2 locks prevent one program from accessing data that has been changed, but not yet committed, by another program.

Locking process is controlled by DB2’s IRLM (Inter System Resource Lock Manager). However, whenever practical, DB2 tries to lock pages without going to the IRLM. This type of lock is called a latch.

Table Space - Recap

Data is actually stored in a structure known as table space. Each table space correlates to one or more individual physical VSAM data sets in the DASD volumes of a storage Group. Each table space contains one or more tables.

There are three different types of table space and are as follows:

1. Simple table space

2. Segmented table space

3. Partitioned table space

---

Simple Table Space

In a simple table space, the space is divided into pages without any higher level structure. Simple table space can contain data from more than one table. As data rows from different tables can reside on the same page, concurrency will be reduced a lot. After DB2 version 2.1, the simple table spaces are almost obsolete.

Segmented Table Space

In a segmented table space, the space is divided into equal sized group of pages called “Segments”. Each segment can contain rows from only one table. This is the most efficient type of table space because it maximizes concurrency.

Partitioned Table Space

In the partitioned table space, the space is divided into units called “Partitions”. Each partition contains part of one table and resides on a separate VSAM data set. Each partition table space can contain only one table. This is suitable for large tables that contain one million or more pages.

---

Lock Size

When a table space is defined or altered, the LOCKSIZE clause specifies a default lock size.

The lock size can be:

• ROW

• PAGE

• TABLE

• TABLESPACE

• ANY

When the LOCKSIZE(ANY) option is used, DB2 selects the optimum lock size for each processing situation.

---

Lock Escalation

Lock hierarchy is as follows:

If the number of locks in one level exceeds an installation default, then DB2 locks a larger unit. This is called Lock escalation.

In a non-segmented table space, the page or row lock is escalated to table space lock. In a segmented table space, the page or row lock is first escalated to table and then if necessary to a table space lock.

---

Lock Duration

Lock duration refers to the length of the time that a lock is maintained. The duration of a lock is based on the BIND options chosen for the program requesting locks. Locks can be acquired either immediately when the plan is requested to be run or iteratively as needed during the execution of the program.

Bind parameters affecting table space and table Locks

• ACQUIRE(ALLOCATE):Locks will be acquired when the plan is allocated, which normally occurs when the first SQL statement is issued. This is used for batch processing.

• ACQUIRE(USE):Locks will be acquired only as they are required, SQL statement by SQL statement. This is used for online processing.

• RELEASE (DEALLOCATE): Locks are not released until the plan is terminated and is used for batch processing.

• RELEASE(COMMIT): Locks are released when a COMMIT is issued and is used for online processing.

Bind parameters affecting page and row Locks is Isolation level

We will see Isolation level and its type in next chapter...

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!