Some questions are very interesting to answer. I just received following question in Email.
What is Live Lock?
A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.
A human example of live lock would be two people who meet face-to-face in a corridor and each moves aside to let the other pass, but they end up moving from side to side without making any progress because they always move the same way at the same time and never cross each other. This is good example of live lock.
Reference : Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Your example was a good one to describe the theory :)
Pinal,
Gud explanation abt live lock. But if you can tell us how to avoid Live lock or is there a way to find it and resolve it? If you have a scrip or something if you can share with us.
Thanks
Live lock is resolved automatically by the SQL Server, when process tries to acquire exclusive lock and denied 4 times, SQL Server Engine release those Shared locks on the resources and allows exclusive lock on the resource.
Can you tell me the way to check the transctions are creatiung blocking and also to see the entire command of text which is doing the steps now
e.g,
dbcc inputbuffer()
through this we generally can’t get the entire operation string which is working. how to retrieve the entire command working?
Hi Pinal,
i have a situation below mention (sql server 2005)
sql session1: where i am inserting the table
begin Tran T1
insert into test values(16,’xyz’)
waitfor delay ’00:00:30′
commit tran t1
sql session2:where i am selecting the data from a table
select * from test
the first session is taking 30 seconds, at the same time if i fired a query on the same table(test) it gives the result while the first session gets completed, so
why begin Trans is locking the whole table instead of row lock, Please give me the idea..
Because the select * statement will also try to select the new inserting row. But SQL Server has applied the Exclusive Lock on the new inserting row already in Session 1. So the select statement in the Session 2 cannot applied the Share Lock on the new inserting row until it run the Commit statement in Session 1.
You may thinking that the new inserting row has not been committed yet so It should be not part of the table “test”. But it is not the case in SQL Server Locking Algorithm. SQL Server just treat the new inserting row as a row data with an Exclusive Lock.
You can have the same result if you replace the insert statement in Session 1 by an statement updating the last row of the table. Both of the cases, SQL Server just apply an Exclusive Lock on the last row of the table.
And if you change the select statement to select top XXX * (without selecting the last row) then there should be no locking issue.
Pinal,
Awesome explanation…..
Can you please provide a live senario example with select statements and some update statements.
–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: queuing.
May I ask what is the need to event another name for lock wait in MS SQL Server documentation? Live lock is an invented term it appears to be, not a term that was used in transaction processing research. I see it in interview questions even. your explanation with humans does have a problem so… databases that implement locking mechanisms do not work like humans . Livelock is a case of resource starvation. are suggesting that SQL Server 2000 and/or 2005 and/or 2008 scheduler and dead lock detection mechanism is faulty? would you be willing to present the test case for live locks in any of these versions please? it would pretty much mean that lock acquisition hierarchy in ms sql server is faulty.
Thank you for the no nonsense example.
this is online community and one has to think twice before putting *nonsense* comments. if anybody wants an example or something they can ask to blogger or search online at any other place.
u have given a nice real life example