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.