Below attempts to help with understanding deadlocks:
Objective
- Collect both books in the order given.
Rules
- Players cannot enter the room at the same time.
- There are only 2 books available.
- When a player picks up a book, they have to hold it for 1 minute before proceeding.
- If a player cannot obtain a book without waiting, the player who entered the room last has to drop their book and restart.
| Player 1 | Player 2 | |
| Time (HH:MM:SS) | Objective: Obtain Book A and then Book B. | Objective: Obtain Book B and then Book A. |
| 10:00:00 | Enters the room. | |
| 10:00:01 | Enters the room. | |
| 10:00:05 | Finds Book A and picks it up. | |
| 10:00:15 | Finds Book B and picks it up. | |
| 10:01:01 | 1 minute has passed. Sees that Book B is held by Player 2 and waits. | |
| 10:01:15 | 1 minute has passed. See that Player 1 has Book A. As per the rules; Player 2 acknowledges that they cannot obtain Book A without waiting and as Player 1 had entered the room first, Player 2 drops their book and exits the room (Player 2 is the DEADLOCK VICTIM). | |
| 10:01:16 | Picks it up Book B. | |
| 10:01:17 | Enters the room. | |
| 10:01:18 | Sees that Book B is held by Player 1 and waits. | |
| 10:02:16 | 1 minute has passed and the objective have been achieved. Drops both books and exits the room. | |
| 10:02:17 | Sees that Book B is available and picks it up. | |
| 10:03:17 | 1 minute has passed and picks up Book A. | |
| 10:04:17 | 1 minute has passed and the objective has been achieved. Drops both books and exits the room. |
If we now think of this in terms of SQL Server, a typical deadlock occurs as follows:
| Session 1 | Session 2 | |
| Time (HH:MM:SS.nnn) | ||
| 10:00:00.000 | Begins Transaction 1. | |
| 10:00:00.001 | Begins Transaction 2. | |
| 10:00:00.002 | Starts a statement updating Table A. | |
| 10:00:00.003 | Starts a statement updating Table B. | |
| 10:00:00.005 | Finished updating Table A and waits to update Table B. | |
| 10:00:00.007 | Finished updating Table B. Needs to update Table A but cannot as it is still held by the Session 1 Transaction 1. Deadlocks, rolls back its changes and exits Transaction 2. | |
| 10:00:00.008 | Starts a statement updating Table B. | |
| 10:00:00.009 | Session reconnects and begins Transaction 3. Sees that Table B is held by Session 1 and waits. | |
| 10:00:00.101 | Finished updating Table B and ends Transaction 1. | |
| 10:00:00.102 | Starts a statement updating Table B. | |
| 10:00.00.106 | Finished updating Table B. Starts a statement updating Table A. | |
| 10:00.00.108 | Finished updating Table A and ends Transaction 3. |
Essentially:
- Multiple change Statements are operating in a single Transaction.
- These Statements are changing more than one object.
- While the Transaction is active, the changed objects are locked to allow for a possible rollback of the Statements.
- Transactions with multiple change Statements are also accessing and locking objects in a different order to the other multiple Statement Transactions.
What you can try do to help reduce Deadlocks:
- Use 1 Statement per Transaction.
- If using multiple Statements per Transaction, try ensure that the Statements in the offending Transactions access the objects in the same order, so you end up with normal waits/blocking instead of Deadlocks.