A deadlock is created when two applications lock data that is needed by the other, resulting in a situation in which neither application can continue executing.
For example, in Figure 1, there are two applications running concurrently: Application A and Application B. The first transaction for Application A is to update the first row in Table 1, and the second transaction is to update the second row in Table 2. Application B updates the second row in Table 2 first, and then the first row in Table 1. At time T1, Application A locks the first row in Table 1. At the same time, Application B locks the second row in Table 2. At time T2, Application A requests a lock on the second row in Table 2. However, at the same time, Application B tries to lock the first row in Table 1. Because Application A will not release its lock on the first row in Table 1 until it can complete an update to the second row in Table 2, and Application B will not release its lock on the second row in Table 2 until it can complete an update to the first row in Table 1, a deadlock occurs. The applications wait until one of them releases its lock on the data.
Because applications do not voluntarily release locks on data that they need, a deadlock detector process is required to break deadlocks. The deadlock detector monitors information about agents that are waiting on locks, and awakens at intervals that are specified by the dlchktime database configuration parameter.
If it finds a deadlock, the deadlock detector arbitrarily selects one deadlocked process as the victim process to roll back. The victim process is awakened, and returns SQLCODE -911 (SQLSTATE 40001), with reason code 2, to the calling application. The database manager rolls back uncommitted transactions from the selected process automatically. When the rollback operation is complete, locks that belonged to the victim process are released, and the other processes involved in the deadlock can continue.
To ensure good performance, select an appropriate value for dlchktime. An interval that is too short causes unnecessary overhead, and an interval that is too long allows deadlocks to linger.
In a partitioned database environment, the value of dlchktime is applied only at the catalog database partition. If a large number of deadlocks are occurring, increase the value of dlchktime to account for lock waits and communication waits.
- Use the FOR UPDATE clause when performing a select operation. This clause ensures that a U lock is set when a process attempts to read data, and it does not allow row blocking.
- Use the WITH RR or WITH RS and USE AND KEEP UPDATE LOCKS clauses in queries. These clauses ensure that a U lock is set when a process attempts to read data, and they allow row blocking.
In a federated system, the data that is requested by an application might not be available because of a deadlock at the data source. When this happens, the Db2® server relies on the deadlock handling facilities at the data source. If deadlocks occur across more than one data source, the Db2 server relies on data source timeout mechanisms to break the deadlocks.
To log more information about deadlocks, set the value of the diaglevel database manager configuration parameter to 4. The logged information includes the name of the locked object, the lock mode, and the application that is holding the lock. The current dynamic SQL and XQuery statement or static package name might also be logged.