What is a live lock ?

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.