Comparison between DB2 database and Oracle concurrency control (LOCK)

The following articles mainly describe the comparison of concurrency control (LOCK) between DB2 database and Oracle. The following describes the comparison of concurrency control (LOCK) between DB2 database and Oracle, I hope it will help you. The following is the main content of the article.

1 Introduction

The minimal recovery and transaction units in relational databases (DB2, Oracle, Sybase, Informix, and SQL Server) are a transaction (Transactions). Transactions are ACID (atomicity, consistency, isolation, and durability) feature. In order to ensure the correctness of concurrent users when accessing the same database object (that is, no loss updates, repeatable reads, no dirty data reads, no Phantom reads ), the concurrency (LOCK) mechanism is introduced in the DB2 database. There are two basic lock types: Exclusive locks (marked as X locks) and Share locks (marked as S locks ).

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.

2 Introduction to the multi-granularity blocking mechanism of DB2

2.1 Lock Object

DB2 supports locking table space, tables, rows, and indexes (DB2 databases on the mainframe can also support locking data pages) to ensure the concurrent 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.

2.2 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.

2.2.1 DB2 table lock mode

For Table-level locking, DB2 can use the following locking methods:

Table 1: DB2 database 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.

2.2.2 DB2 row lock mode

In addition to table locks, DB2 also supports row locks in the following ways. The above content is a comparison of the concurrency control (LOCK) of the DB2 database and the Oracle database. I hope you will gain some benefits.