THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: UPDATE Locks

UPDATE locks are not really a separate kind of lock, but rather are a hybrid of SHARED and EXCLUSIVE locks. And contrary to what you might think, UPDATE locks are not just acquired for UPDATE operations.  A transaction acquires this kind of lock when SQL Server executes a data modification operation but first needs to perform a search to find the resource to modify.

While SQL Server is searching, it shouldn’t need to acquire an EXCLUSIVE lock; it only needs the EXCLUSIVE lock when the data to be changed is found. Normally, if a SQL Server process was just searching for data, it would acquire a SHARED lock on each resource it encounters and then determines whether it has found the data it is searching for. However, if SQL Server started out with a SHARED lock while searching for the data to modify, there are potential problems. A situation could occur where two processes were both searching for the same resource to modify (for example, the same customer row in the Customers table), using different access paths, and they could both reach the desired resource at the same time. If they both were acquiring SHARED locks on the data they were examining, they could both lock the resource they wanted to change, but before they made the modification they would have to convert their lock to an EXCLUSIVE lock. Since the other process would have a SHARED lock, no EXCLUSIVE lock could be granted. Each process would have a SHARED lock, and each would try to change it to an EXCLUSIVE lock, but neither could proceed because of the presence of the other. This is a deadlock situation, called a ‘conversion deadlock’. 

UPDATE locks are really a deadlock avoidance mechanism. If SQL Server uses UPDATE locks, a deadlock will NOT occur. If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires UPDATE locks until it finds the data to modify. UPDATE locks are compatible with SHARED locks, but are not compatible with EXCLUSIVE locks or other UPDATE locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an UPDATE lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change.

In the sys.dm_tran_locks view, a request_mode value of ‘U’ indicates an UPDATE lock.

So let's look at UPDATE locks:

[I am using the old sample database pubs. If you want to try this exact code, you can download pubs from here.]

-- Close all existing connections and start a new one

-- Step 1:
USE pubs;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


BEGIN TRAN;
UPDATE authors
SET contract = 0
WHERE au_lname = 'Ringer' ;

-- -- Step 2: Open a second connection window uncomment once,
-- -- 
so the ROLLBACK is still commented. Execute…

--USE pubs;
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

--BEGIN TRAN;
--UPDATE  authors
--SET city = 'Provo'
--WHERE  state = 'UT';

-- -- You should be blocked.

-- -- ROLLBACK TRAN;

-- Step 3: Go back to the first connection window and run the following
SELECT request_session_id AS session_id, DB_NAME(resource_database_id) AS [database],
   request_mode AS mode, resource_type as [type], 
   resource_associated_entity_id AS entity,
   resource_description,  request_status AS status
FROM sys.dm_tran_locks;
COMMIT TRAN;

You should see output similar to this:

image

Note the U lock with the status of WAIT for a KEY, with the same resource description as a KEY lock that the first connection has  been GRANTed. Now COMMIT or ROLLBACK the first connection,  and you should see the second connection will get the X lock on the KEY it is waiting for, plus the X lock on the other KEY.

image

I mentioned that UPDATE locks indicated an 'intention of eventually modifying data', so you might think that UPDATE locks are similar to INTENT locks. Not really… UPDATE locks indicate an intention to change the lock mode, whereas INTENT locks indicate an intention to change the lock granularity.

Wishing you maximum concurrency,

~Kalen

Published Friday, November 13, 2009 2:48 PM by Kalen Delaney
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

GeBaGong said:

"and then the second process could not get any lock and would wait for the first process to be done.Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock"

but the second process haved shared lock.How can the first process convert its Update lock to an Exclusive lock? i know,  TRANSACTION ISOLATION LEVEL is READ COMMITTED,update lock can avoid dead lock,but when level is higher than "read committed", dead lock occurs again.

November 16, 2009 6:58 AM
 

Kalen Delaney said:

Hi GeBaGong

You are right, I was assuming that you are running in the default isolation level  READ COMMITTED. If you choose to run in a higher level, one of the tradeoffs is that you will have more blocking and possibly more deadlocks.

~Kalen

November 16, 2009 4:54 PM
 

Kalen Delaney said:

In the last couple of months, I've told you about Intent Locks and UPDATE locks. I want to just provide

November 19, 2009 7:33 PM
 

Kalen Delaney said:

In the last couple of months, I've told you about Intent Locks and UPDATE locks. I want to just provide

November 19, 2009 7:35 PM
 

Munish Sharma said:

Kalen, I tried above scenario and found one difference that in actual second update transaction put IX lock at the PAGE level.

April 17, 2013 12:42 AM
 

Kalen Delaney said:

Hi Munish

I'm not sure what you mean by the second update transaction. I just ran my code on SQL2008R2SP2 and got the same results as above.

~Kalen

April 17, 2013 1:18 PM
 

Jeff Fischer said:

Hi Kalen,

The font you're using for your SQL text is horrible on my monitor.  A cleaner font would be great!

June 5, 2013 3:22 PM
 

Kalen Delaney said:

Hi Jeff

What would be an example of a 'cleaner' font?

You can always cut and paste into a query window which you have configured to use a font of your own choosing! All my code is intended to be run by readers.

Thanks

Kalen

June 5, 2013 3:45 PM
 

ashish said:

Can an update lock be asked for a process that does no update ? No Lock hint provided either. We have deadlock scenarios where a select query seems to have an update lock on an index , which another process (this one is an update) wants an exclusive lock on, causing a deadlock. Cannot figure out why my select acquires an UPDATE LOCK

January 9, 2014 11:19 AM
 

Kalen Delaney said:

Hi Ashish

I don't see how you could get an update lock from a SELECT without a hint. You'll need to trace the deadlock and see exactly what statements are executed leading up to the deadlock, and what statements are acquiring the locks involved.

January 9, 2014 8:13 PM
 

ashish said:

Thanks. Yes that's what is happening! The stored proc in question is called from various other stored procs , some of which do updates.  I have made some changes to my code, that should hopefully resolve my deadlocks. Thanks for your quick response!

January 10, 2014 11:32 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement