Deadlock in Single Query Transactions
If single query transactions are accumulating page level locks one at a time, deadlocks can still occur. At least two transactions must be involved and one transaction must be modifying rows.
Deadlock can occur during a single query transaction when:
• Different access paths to pages in the base table are used
• Lock escalation occurs
Lock escalation can be caused by any of the following:
• Converting shared lock to exclusive lock
• Overflow chains
• System lock limits exceeded
• MAXLOCKS exceeded
• B-tree index splits
Different Access Paths as a Source of Deadlock
Multiple transactions updating table data using different access paths can cause single query deadlocks.
In the following example, the EMP table has an ISAM base table structure on name and a hash index on empno.
1. User1 accesses the EMP table through the empno index and is granted an exclusive lock on the fourth page of the table.
2. User2 accesses the EMP table through the ISAM base table structure and is granted an exclusive lock on the third page
3. User1 needs an exclusive lock on the third page, but cannot get one because User2 already has a lock on it.
4. User2 needs an exclusive lock on the fourth page, but cannot get one because User1 already has a lock on it.
Lock Escalation as a Source of Deadlock
When multiple transactions are updating a table and lock escalation occurs, deadlock can occur. Causes of lock escalation deadlock are:
• A transaction has run into a lock limit and can only continue by escalating to a table-level lock.
• More than MAXLOCKS pages need to be locked.
• Long overflow chains
If locking limits are reached, increase the limit or change the locking strategy, for example: take table locks at the start of the transaction.
Consider the following example in which two users are trying to insert into the same table that has many overflow pages:
1. User1 inserts a record. Because of a long overflow chain, exclusively locks ten pages.
2. User2 also inserts a record and is granted locks down another overflow chain.
3. User1’s transaction reaches MAXLOCKS pages and needs to escalate to an exclusive table-level lock.
4. User2 still holds an intent exclusive (IX) lock on the table, resulting in User1’s request to wait.
5. User2’s query also needs to lock more than MAXLOCKS pages; escalation to an exclusive table level lock is required. User2’s request is also blocked, because User1 is holding an intent exclusive (IX) lock on the table.
6. Deadlock occurs.
Overflow Chains and Locking
Tables with overflow pages can cause locking problems because all pages in the overflow chain must be locked when a row in the chain is accessed.
Escalation to table-level locking while locking an overflow chain can cause deadlock. If you have a table with many overflow pages that are still present after a modify, either increase the table page size to allow more rows on a page or use the SET LOCKMODE statement as follows:
• Establish table-level locking as the default for that table
• Increase MAXLOCKS
Last modified date: 11/28/2023