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