SQL Deadlock Deadlock When using a locking scheduler

SQL – Deadlock

Deadlock When using a locking scheduler, there is the possibility of deadlock. ◦ Deadlock occurs when transactions are waiting for locks to be released, but they are holding the locks that other transactions need to continue. How do we prevent and/or fix deadlock situations? Detection is relatively easy, build a Wait-For graph. ◦ A Wait-For graph is a graph of what transactions each transaction is waiting to release a lock. If there is a cycle in the Wait-For graph, you have deadlock.

Which transactions are deadlocked? T 7 T 2 T 1 T 4 T 3 Wait-For graph T 5 T 6 T 1; T 2; T 3; T 5; T 6; T 4; T 7; All of them

So what do we do now? If you find a deadlock cycle, there is only one thing you can do, rollback. But which transactions do you do a rollback on? Simple solution (timeout), if a transaction has been waiting for X seconds, roll it back. ◦ But choosing X is hard. ◦ This solution won't fix the problem if the rollback transactions are just resubmitted leading to the same deadlock situation.

Wait-Die (Deadlock Resolution) Each transaction Ti is given a timestamp when it starts, denoted ts(Ti) Suppose Ti requests a lock currently held by Tj If ts(Ti) < ts(Tj): ◦ then Ti waits for Tj (older waits for younger) Else: ◦ Ti dies (aborts) (younger dies) If Ti dies then it later restarts with the same timestamp

Wait-Die Example T 1 (ts = 10) wait T 2 (ts = 15) wait T 3 (ts = 20)

Wound-Wait (Deadlock Resolution) Each transaction Ti is given a timestamp when it starts, denoted ts(Ti) Suppose Ti requests a lock currently held by Tj If ts(Ti) < ts(Tj): ◦ then Ti wounds Tj (younger yields lock to older) ◦ Wound: Tj rolls back and gives lock to Ti Else: ◦ Ti waits (younger waits for older) If Tj dies then later it restarts with the same timestamp

Wound-Wait Example T 1 (ts = 20) wait T 2 (ts = 15) wait T 3 (ts = 10) NOTE: Different timestamps from before!

Why is reusing the old timestamp important? Deadlock resolution favors older transactions, eventually the rollbacked transaction will be the oldest If you gave it new timestamps, it could recreate the deadlock Penalizing roll backed transaction with a new timestamp is like kicking a puppy Reduce; Reuse; Recycle: Do your part

Deadlock Resolution Strategies Wait-Die and Wound-Wait ensure no starvation ◦ Starvation is when objects are less efficient in their work because they are waiting for resources. Deadlock is the most extreme form of starvation. Wait-Die tends to roll back more transactions than Wound. Wait but they tend to have done less work Wait-Die and Wound-Wait are easier to implement than Waits. For graph Waits-For graph technique only aborts transactions if there really is a deadlock (unlike the others)