Deadlock and Live Lock

Question

Hi
What is the Deadlock and Live Lock in SQL Server. How will you go about the Resolving Deadlocks ?

Jignesh Trivedi · Answer

hi,
Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue.
For live lock please refer,
http://blog.sqlauthority.com/2008/03/21/sql-server-introduction-to-live-lock-what-is-live-lock/
To avoide Deadlocks
- Minimize the size and time of transaction.
- use nolock and rowlock with query if possible
select * from table1 (nolock)
- Select deadlock victim by using SET DEADLOCK_PRIORITY.
hope this help.

Senthilkumar · Answer

Hi Arun,
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user's process.for more details click here
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Deadlock occurs when two user processes/transactions have locks on 2 separate objects and each process is trying to acquire a lock on the object that has been acquired by the other process. In such a scenario each process is waiting for the other process to release the lock to acquire a lock on the object itself. When a request for exclusive lock is denied again and again because a series of overlapping shared locks are interfering with each other and to adapt from each other they keep on changing the status, it is known as live lock.
One can resolve deadlocks by using TRY CATCH blocks. If the code inside a TRY statement fails, the CATCH automatically catches the control of the flow letting the transaction rollback and resume execution.
For more info:
http://dba.fyicenter.com/Interview-Questions/SQL-Server-DBA/What_is_a_deadlock_and_what_is_a_live_lock_How_.html