What is a live lock ?

 Posted by Bharathi Cherukuri on 6/11/2012 | Category: Sql Server Interview questions | Views: 2227 | Points: 40
Answer:

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.

Example:

This is explained clearly from the below example:


--1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM authors

--2
UPDATE authors
SET au_lname = 'X'
WHERE au_id = '238-95-7766'

--3
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM authors

--4
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM authors

--5
EXEC sp_who2


You will see that 2 is blocked by 1, 3 is blocked by 2 and 4 is blocked by 3. Theoretically, 3 and 4 could execute (there's only a shared lock), but queuing makes 3 and 4 to wait. This is how livelocks are avoided.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response