Deep Analysis of DB2 Lock Mechanism

The DB2 lock mechanism plays an important role in the DB2 database. Let's take a look at what the DB2 lock mechanism is.

In relational databases DB2, Oracle, Sybase, Informix, and SQL Server, the minimal recovery and transaction unit is a transaction Transactions. Transactions are characterized by ACID (atomicity, consistency, isolation, and durability. In order to ensure the correctness of concurrent users when accessing the same database object, that is, there is no loss of updates, repeatable reads, no "dirty" data, and no "phantom" reads ), the database introduces the concurrent lock mechanism. There are two basic lock types: Exclusive locks (X locks) and Share locks (S ).

Exclusive lock: If transaction T applies the X lock to data D, no other transaction can apply any type lock to D Until T releases the X lock on D; generally, an exclusive lock must be applied to the data before the data is modified. Therefore, an exclusive lock is also called a write lock.

Share lock: If transaction T adds S lock to data D, other transactions can only apply S lock to D, but cannot apply X lock until T releases S lock on D; generally, you need to apply a shared lock to the data before reading the data. Therefore, a shared lock is also called a read lock.

Introduction to the multi-granularity blocking mechanism of DB2

Fenced1 Lock Object

DB2 supports locking tables, tables, rows, and indexes. databases on the mainframe can also lock data pages to ensure the concurrency integrity of the database. However, when considering the concurrency of your applications, we usually do not check the locks used for tablespaces and indexes. This type of problem analysis focuses on table locks and row locks.

Fenced2 lock Policy

DB2 can lock only the table or the rows in the table. If only the table is locked, all rows in the table are affected to the same extent. If the lock range is for tables and subordinate rows, the corresponding data rows will be locked after the table is locked. Whether an application adds a row lock or a table lock or a row lock is determined by the Command executed by the application and the isolation level of the system.

Fencedfenced1 DB2 table lock mode

The following describes several table lock modes:

The IS, IX, and SIX methods are used at the table level and require row lock cooperation. They can prevent other applications from adding an exclusive lock to the table.

If an application acquires the IS lock of a table, the application can obtain the S lock on a row for read-only operations, and other applications can also read this row, or modify other rows in the table.
If an application acquires the IX lock of a table, the application obtains the X lock on a row for the change operation, and other applications can read or change other rows in the table.
If an application acquires the SIX lock of a table, the application can obtain the X lock on a row for modification. Other applications can only perform read-only operations on other rows of the table.
The S, U, X, and Z modes are used at the table level, but do not require row lock cooperation. They are relatively strict table lock policies.

If an application obtains the S lock of a table. This application can read any data in the table. It also allows other applications to obtain the read-only request lock for the table. If an application needs to change the data read from the table, it must wait until the S lock is released.
If an application obtains the U Lock of a table, the application can read any data in the table and obtain the X lock of the table to obtain the modification permission of any data in the table. Other applications can only read data from this table. The difference between the U Lock and the S lock lies in the intention of modification. The U Lock is designed to prevent two applications from simultaneously applying for the X lock when they own the S lock.
If an application obtains the X lock on a table, the application can read or modify any data in the table. Other applications cannot read or modify the table.
If an application obtains the Z lock on a table, the application can read or modify any data in the table. Other applications, including uncommitted read programs, cannot read or modify the table.
The IN lock is used for the table to allow uncommitted read.

Fencedfenced2 DB2 row lock mode

Compatibility of fencedfenced3 DB2 locks

Fenced3 DB2 lock upgrade

Each lock requires a certain amount of memory space in the memory. To reduce the memory overhead required by the lock, DB2 provides the lock upgrade function. A lock upgrade adds a non-intention table lock to the table and releases the row lock to reduce the number of locks, thereby reducing the memory overhead required by the lock. The lock upgrade is automatically completed by the Database Manager. The configuration parameters of two databases directly affect the lock upgrade process:

Locklist -- the memory used to lock storage in the global memory of a database. Unit: Page 4 K ).

Maxlocks-the percentage of memory occupied by locklist allowed by an application.

The lock upgrade is triggered in either of the following situations:

The memory space occupied by the lock requested by an application exceeds the product size of maxlocks and locklist. In this case, the database manager tries to save space by applying for a lock request for the application and releasing the row lock.
The memory space occupied by all locks in a database exceeds the size defined by locklist. In this case, the database manager will try to save space by applying for a lock request for the application and releasing the row lock.
Although the lock upgrade will reduce the concurrent performance of the OLTP application, after the lock upgrade, the lock will release the memory occupied by the lock and increase the memory space of the available lock.
Lock upgrades may fail. For example, an application now has an IX lock on a table, and some rows in the table have an X lock, another application requests the IS lock on the table and the S lock on many rows, which causes the lock upgrade due to the number of applied locks. The database manager tries to reduce the number of required locks for the S lock on the application form. However, the S lock conflicts with the original IX lock on the table, and the lock upgrade fails.

If the lock upgrade fails, the application that causes the lock upgrade will receive a-912 SQLCODE. After the lock upgrade fails, DBA should consider increasing the locklist size or increasing the maxlocks percentage. At the same time, programmers can roll back the program after the lock upgrade and resubmit the transaction in the program, for example, if sqlca. sqlcode =-912 then rollback and retry ).

Monitoring of Multi-granularity blocking mechanism in DB2

There are two ways to monitor locks in DB2: Snapshot monitoring and event monitoring.
 

Implementation of adding verification constraints to DB2

Use DB2 sequence to automatically generate a primary key

How to Create a tablespace in DB2

Introduction to the DB2 user group in Linux

Top 10 considerations for DB2 Performance Optimization