SQL SERVER – Introduction to Live Lock – What is Live Lock?

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 (http://blog.SQLAuthority.com)

14 thoughts on “SQL SERVER – Introduction to Live Lock – What is Live Lock?

  1. 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

    Like

  2. 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.

    Like

  3. 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?

    Like

  4. 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..

    Like

    • –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.

      Like

  5. 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.

    Like

  6. Pingback: SQL SERVER – Locking and Blocking – Important Aspect of Database and Effect on Performance – Quiz – Puzzle – 5 of 31 « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Locking, Blocking and Deadlock – Quiz – Puzzle – 9 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s