Deadlock

1. Deadlock

2. Situations • 4-way stops in Seattle streets • “When two trains approach each other at a crossing, both shall come to a full stop and neither shall start up again until the other has gone.” - Statute passed by the Kansas (USA) State Legislature, early in the 20th century • Priority-based interrupts: a critical program and KB driver • A situation wherein two or more competing actions are waiting for the other to finish, and thus neither ever does -Wikipedia

3. Starvation • Starvation occurs when one or more threads in your program are blocked from gaining access to a resource and, as a result, cannot make progress. • Deadlock, the ultimate form of starvation, occurs when two or more threads are waiting on a condition that cannot be satisfied. Deadlock most often occurs when two (or more) threads are each waiting for the other(s) to do something • Occurs most commonly in multitasking and client/server environments

4. Example • A program waiting for output from a server while the server is waiting for more input from the controlling program before outputting anything. • This type of deadlock is sometimes called a "starvation deadlock", though the term "starvation" is more properly used for situations where a program can never run simply because it never gets high enough priority

5. Starvation • Each process is trying to send stuff to the other but all buffers are full because nobody is reading anything

6. Example • Two processes that are sharing some resource (e.g. read access to a table) but then both decide to wait for exclusive (e.g. write) access – Deadly embrace

7. Example • Client applications using the database may require exclusive access to a table, and in order to gain exclusive access they ask for a lock. • If one client application holds a lock on a table and attempts to obtain the lock on a second table that is already held by a second client application, this may lead to deadlock if the second application then attempts to obtain the lock that is held by the first application

8. Deadlock • Preventing deadlock • Allow users to issue all lock requests at one time • Require all application programs to lock resources in the same order • Breaking deadlock • A DBMS has algorithms for detecting deadlock • When deadlock occurs, DBMS aborts one of the transactions and rollbacks partially completed work

9. Deadlock

10. Deadlock

11. Optimistic versus Pessimistic Locking • Optimistic locking assumes that no transaction conflict will occur: • DBMS processes a transaction; checks whether conflict occurred: • If not, the transaction is finished • If so, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur: • Locks are issued before a transaction is processed, and then the locks are released • Optimistic locking is preferred for the Internet and for many intranet applications

12. Optimistic Locking

13. Pessimistic Locking

14. Declaring Lock Characteristics • Most application programs do not explicitly declare locks due to its complication • Instead, they mark transaction boundaries and declare locking behavior they want the DBMS to use • Transaction boundary markers: BEGIN, COMMIT, and ROLLBACK TRANSACTION • Advantage • If the locking behavior needs to be changed, only the lock declaration need be changed, not the application program

15. Marking Transaction Boundaries BEGIN PRODUCT.Name, PRODUCT.Quantity FROM PRODUCT WHERE PRODUCT.Name = ‘Pencil’ SET NewQuantity = PRODUCT.Quantity – 5 UPDATE PRODUCT SET PRODUCT.Quantity = NewQuantity WHERE PRODUCT.Name = ‘Pencil’ //…. continue processing transactions ….. IF transaction has completed normally THEN COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END IF

16. SQL Server – Deadlock • When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. • The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

17. What an application should do when it happens: • Resubmit the aborted transaction, which most likely can now run successfully. • This process, if it happens often on your server, can drag down performance. • If the application has not been written to trap deadlock errors and to automatically resubmit the aborted transaction, users may very well become confused as to what is happening when they receive deadlock error messages on their computer.

18. Guidelines • Normalize databases • Have the application access server objects in the same order each time. This will reduce confusion • During transactions, don't allow any user input. Collect it before the transaction begins • Keep transactions as short as possible • Use stored procedures or keep transactions with a single batch, thereby reducing the number of round trips between the application and the SQL server • Minimize the number of reads on the same data; cache it by storing it in a variable or an array, and then re-reading it from there

19. Guidelines – conti. • Reduce lock time. Lock it as late as possible and unlock it as soon as possible. • Reduce lock escalation by using the ROWLOCK or PAGLOCK. • The default lock for SQL 2000 is RowLock. However, if the number of transmission increase, SQL will escalate the lock level to table level. It will certainly speed the transmission but the downside is that it will lock out other users for longer than you may intended to.