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.

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.

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?

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.

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 –

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

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.