SQL SERVER – Difference Between Update Lock and Exclusive Lock

I have often got this question on this blog as well in different SQL Training.

What is the difference between Update Lock and Exclusive Lock?

When Exclusive Lock is on any processes no other lock can be placed on that row or table. Every other process have to wait till Exclusive Lock is complete its tasks.

Update Lock is kind of Exclusive Lock except it can be placed on the row which already have Shared Lock on it. Update Lock reads the data of row which has Shared Lock, as soon as Update Lock is ready to change the data it converts itself to Exclusive Lock.

In some other posts, we will discuss locks in more details. Let me know if my above explanation is clear enough.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Difference Between Update Lock and Exclusive Lock

  1. Hi,

    I think I got it but just one thing: If update lock is already placed on a row can subsequent shared or update locks be placed on the same row?

    Like

    • Hi Marko,

      An UPDATE lock cannot be obtained on the resource until the other UPDATE lock has been released. Ultimately, you can only have one UPDATE lock on a resource at any given time. An UPDATE lock is a lot like a shared lock only it has to make sure it can escalate to an exclusive lock.. To prevent a shared lock/dead lock scenario, only one transaction at a time can obtain an UPDATE lock on the resource.

      Like

  2. Hello!

    I believe the key would be to understand that data is not updated untill an Exclusive lock is actually obtained. Pinal, please correct me if I am wrong, but an Update lock is actually an Intent Exclusive lock (IX). Am I understanding this concept correctly?

    Thank-you, and have a great day!

    Like

  3. Hello Nakul,

    You are correct in saying that data is not updated until an Exclusive lick is not obtained. Any modifiction to a row can be implemented using Exclusive lock only. But Update lock is not Intent Exclusive lock as intent locks are implemented at higher levels (like page, table or database). We can understand update lock as future intent exclusive lock.

    Regards,
    Pinal Dave

    Like

  4. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  5. Lets assume 5 process wants to access some data in a particular table.

    A shared lock is issued when you want to allow all the process to read concurrently. Shared lock in other words can be called read only mode. No updates/changes to the existing data is allowed when shared lock is on.

    Now assume one or more of the above process wants to update a particular row, they cannot update readily because the shared lock is on. One has to internally obtain a exclusive lock before it can go a head and update. So it places a request to the server to issue a exclusive lock. This is what is called “Update lock” stage.

    Update lock is a internal locking done to avoid deadlock stage i.e for suppose assume 3 process among 5 want to update the data. These three process request the server to issue a exclusive lock which the server cannot issue readily because the other 2 process are still reading the data and shared lock is still on.

    Now the point here is that when the 2 other process finishes reading the document and shared lock is removed which process has to get the exclusive lock first and perform the update operation?(Note that only one lock can exist at a single point of time just similar to a person cannot be at 2 different places at the same time. So to issue exclusive lock to one process shared lock has to be removed first. Also only one process can be allowed to update the date on the table by issuing it a exclusive lock to avoid concurrency problems. For more info on concurrency problems refer – http://msdn.microsoft.com/en-us/library/aa213029(v=sql.80).aspx)

    To solve this confusion update lock is used. The system issues update lock to one of the process that requested for the exclusive lock depending on preference type set which can be either first in first out, top priority first etc (For more info on how tasks are scheduled refer to: http://en.wikipedia.org/wiki/Scheduling_(computing)#Scheduling_disciplines)

    Once a update lock is issued to one of the above 3 process, it obtains exclusive lock immediately after 2 other process exit reading there by removing the shared lock placed on the data.

    P.S- I’m not a professional in SQL but this is what I understood from my research

    Like

  6. Hi Pinal,
    First of all a big thankyou…..for your articles…
    Can you please write an article on all kinds of locks…starting from basics….

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  8. Hi Pinal, Firstly thanks for this article.
    I have a requirement in which i have to read data from first table, then use this data to perform some query for another table, after the query executed successfully update the data of the first table.. What kind of lock are used in select and update query.
    Needed help..??
    Thanks in advance..

    Like

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