DB2 guarantees data integrity by using several locking mechanisms. These strategies permits multiple users from multiple environments to access and modify data concurrently without lose of data integrity.

Why Data Locking?

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 differentiate 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 and could get modified. If two users tries to update the same record at the same time, or if one user deletes a record while some other user is trying to update it, without proper locking strategy and locking mechanisms, the data integrity can go haywire. To avoid such situations a DBMS uses a locking mechanism.

Locking Mechanism

DB2 supports locking at three levels. Table space level, table level and page level. More precisely, DB2 locks are enacted on data stored in one of the following formats: Tablespace page. Complete tablespace, Table in a segmented tablespace. Index sub-page. This list is 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 a lower level without compatible higher-level lock also being taken. For example, you can take a tablespace lock without taking any other lock, but for taking a page lock you have to first get a tablespace lock. Many modes of locks are supported in DB2, but they can be classified into two major classes:

  • Locks to enable the reading of data
  • Locks to enable the updating of data

The different locks at the tablespace, table and page level are illustrated in the following tables:

Tablespace and Table Locks

Tablespace and Table Locks

Locks Vs Latches

A true lock is handled by DB2 using IMS Resource Lock Manager. However, whenever it is practical, DB2 will try to lock pages without going to IRLM. This type of lock is referred to as a latch. True locks are always set in the IRLM. Latches, by contrast, are set internally by DB2 without going to the IRLM. When a latch is taken instead of a lock, it is handled by internal DB2 code. So cross-memory service calls to the IRLM is eliminated. Also a latch requires about one-third the number of instructions as a lock. Therefore, latches are more efficient than locks because they avoid the overhead associated with calling an external address space.

Latches are used when a resource serialization is required for a short time. Prior to DB2 V3, latches were generally used to lock only DB2 index pages and internal DB2 resources. When running V3, DB2 uses latching more frequently. This includes data page latches. Both latches and locks guarantee data integrity.

Deadlocks and Timeouts

Locks can be used to solve problems of concurrency, and ensure data integrity. But unfortunately, locking introduces a problem called deadlock. Deadlock is a situation in which two or more transactions are in simultaneous Wait State, each waiting for one of the others to release a lock before it can proceed. If a deadlock occurs the system will detect and break it. Breaking a deadlock involves choosing one of the deadlocked transactions as the victim and depending on the transaction manager concerned, either rolling it back automatically or requesting it to roll back itself. Either way, the transaction will release its locks and thus allow some other transaction to proceed. In general, therefore, any operation that requests a lock may be rejected with a negative SQLCODE indicating the transaction has been selected as the victim of a deadlock situation and has either been rolled back or requested to roll back.

The problem of deadlock is thus significant because the application programs may need to include explicit code to deal with it if arises. A time-out is caused by the unavailability of a given resource. Time outs are caused when a transaction has to wait for a resource, which is held by another transaction. The duration of the waiting time, after which the time-out should occur, is determined by the IRLMRWT DSNZPARM parameter.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Mainframe Topics