Deadlocks

A deadlock is the one locking problem DB2 cannot wait out. When two programs each wait for a lock the other one holds, nobody can move forward.

What is a deadlock

• When two or more transactions are in a simultaneous wait state for each other's locks, a deadlock occurs.
• Example: program A locks row 1 and wants row 2, while program B locks row 2 and wants row 1. Both wait forever.
• A deadlock is different from a plain wait. In a plain wait the other program will finish and release the lock. In a deadlock nobody ever finishes.
• Deadlocks happen most with long transactions, tables accessed in different orders, and S to X lock promotion by two programs at once.

How DB2 resolves a deadlock

• IRLM watches the wait graph and detects the circular wait. It then picks one transaction as the victim.
• The victim gets SQLCODE -911 (rollback already done by DB2) or SQLCODE -913 (no rollback done, your program must issue ROLLBACK).
• The surviving transaction keeps its locks and continues normally.
• Your program should trap -911 and -913 and retry the unit of work from the last commit point. Example:-
  EVALUATE SQLCODE WHEN -911 DISPLAY 'DEADLOCK VICTIM - DB2 ROLLED BACK, RETRYING' PERFORM RETRY-UNIT-OF-WORK WHEN -913 DISPLAY 'DEADLOCK VICTIM - ISSUING ROLLBACK, RETRYING' EXEC SQL ROLLBACK END-EXEC PERFORM RETRY-UNIT-OF-WORK WHEN OTHER DISPLAY 'SQL ERROR: ' SQLCODE END-EVALUATE

How to avoid deadlocks

• Keep transactions short and COMMIT often. Short transactions hold locks for less time.
• Access tables and rows in the same order in every program. If all programs lock EMP before DEPT, the circle cannot form.
• Avoid LOCK TABLE IN EXCLUSIVE MODE in online programs. It forces every other program to queue behind you.
• Use Cursor Stability instead of Repeatable Read unless you truly need the stronger guarantee.
• For retry logic, add a small wait and a retry counter so a repeated deadlock does not loop forever.