DB2 lock compatibility and deadlock

What is DB2 lock compatibility? What is the deadlock? These questions are answered in detail below. If you have encountered a similar problem in this regard, take a look.

Lock compatibility

If one lock status on the data resource allows another lock to be placed on the same resource, the two locks or two statuses are considered to be compatible. Whenever a transaction holds a lock on a data resource and the second transaction requests a lock on the same resource, the DB2 database manager checks the two lock States to determine whether they are compatible. If the lock is compatible, the lock is granted to the second transaction assuming that no other transaction is waiting for the data resource ). However, if the lock is incompatible, the second transaction must wait until the first transaction releases its lock before obtaining access to the resource and continuing to process it. If there are multiple locks on the resource that are not compatible with the new request locks, the second transaction must wait until they are all released .) See IBM DB2 Universal Database Administration Guide: Performance document or search for Lock type compatibility topics in the DB2 Information Center) to obtain specific information about compatibility between locks.

Lock upgrade

All Locks require storage space. Because the available space is not infinite, the DB2 database manager must restrict the space available for the lock, which is completed through the maxlocks database configuration parameters ). To prevent the specified database Proxy from exceeding the lock space limit, the lock upgrade process is automatically executed when there are too many locks of any type. A lock upgrade is a type of conversion. It converts several individual row-level locks in the same table into a single table-Level Lock. Because the lock upgrade is internally processed, the only result that can be detected externally may be less concurrent access to one or more tables.

The following is how the lock upgrade works: when the transaction request lock and the lock storage space is full, select a table associated with the transaction to help it obtain a table-Level Lock, release all row-level locks of the table to create space in the lock list data structure) and add the table-level locks to the lock list. If the space released by this process is insufficient, select another table and repeat the process until sufficient available space is released. At this time, the transaction will get the requested lock and continue to execute. However, if the necessary available lock space is still not obtained after all row-level locks of the transaction have been upgraded, the SQL error code is used) the transaction must commit or roll back all changes made since it was started, and then terminate the transaction.

Deadlock

Sometimes two or more transactions compete for locks, causing a deadlock. The best way to explain the cause of the deadlock is to give an example: Assume that transaction 1 acquires the mutex X lock on Table A, and transaction 2 acquires the mutex X lock on table B. Now, assume that transaction 1 tries to obtain the mutex X lock on table B, and transaction 2 tries to obtain the mutex X lock on Table. The processing of both transactions will be suspended until the second lock request is agreed. However, since any transaction releases the lock currently held by it through the execution or rollback operation), both of them) the lock requests of any transaction will not be agreed, and because neither transaction can release the lock it currently holds because they are suspended and waiting for the lock), both transactions are deadlocked.

When a deadlock occurs, all transactions involved will wait for the lock to be released indefinitely unless some external agents take action. The tool used by DB2 Universal Database to handle deadlocks is an asynchronous system background process called a deadlock detector. The only role of the deadlock detector is to locate and solve any deadlocks found in the lock subsystem. The deadlock detector is sleep for most of the time, but is "awakened" at the preset interval to determine whether a deadlock exists. If the deadlock detector finds a deadlock in the lock subsystem, select a transaction involved in the deadlock, terminate and roll it back. The transaction to be terminated and rolled back receives an SQL error code, and all locks it receives are released .) Generally, one or more of the remaining transactions can be executed.

Lock timeout

At any time, when a transaction holds a lock on a specific data resource, such as a table or row, until the transaction holding the lock ends and releases all the locks it acquires, access to the resource by other transactions may be rejected. If there is no proper lock timeout detection mechanism, the transaction may wait for the lock to be released indefinitely. For example, a transaction is waiting for another user's application to release the lock, and the user has left his or her workstation, however, I forgot to execute some interactions that allow the application to terminate transactions with locks. Obviously, this type of situation can cause very poor application performance. To avoid blocking the execution of other applications when such a situation occurs, you can specify the lock timeout value in the database configuration file through the locktimeout Database Configuration Parameter ). This parameter controls the time when any transaction will wait to obtain the requested lock. If the desired lock is not obtained after the specified interval, the waiting application receives an error and rolls back the transaction requesting the lock. Distributed Transaction application environments are especially prone to such timeouts; they can be avoided by using lock timeouts.

Deep Analysis of DB2 Lock Mechanism

It is not allowed to set the db2 primary key to solve the problem.

How to Create a tablespace in DB2

Introduction to the DB2 user group in Linux

Install DB2 in linux