THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Geek City: Intent to Update

In the last couple of months, I've told you about Intent Locks and UPDATE locks. I want to just provide a wrap up to those posts that talks about both of these aspects of locking.

You can think of both of these locking aspects as although SQL Server to indicate an intention. However, Intent Locks have to do with the unit of locking and UPDATE locks have to do with the type of lock. 

Here's how you might think about the 'intention':

If you get individual rows locks, you might have the intention of getting more row locks on the same page or table, so you might eventually want to lock the entire page or table. To make sure the page or table is available when you're ready to lock it, SQL Server acquires an Intent lock on the larger units. If you have a row lock, you'll get Intent locks on the page and the table containing that row. Intent locks can go with any type of lock: we can have Intent-Shared (IS), Intent-Exclusive (IX) and even Intent-Update (IU).

UPDATE locks are a way of SQL Server stating your intention to change the type of lock. If you are searching for data to modify, you might intend to eventually get an X lock. So you get U locks while you're searching (instead of S locks)  and then X locks when you find the data to modify.

You might also want to note that although you can get an IU lock on a page, at the table level an IU lock becomes IX. You can see that in the output I showed you in my UPDATE lock posting, which I repeat here:


Session 52 is requesting a U lock on a key, and it already holds an IU lock on the page that contains the key, but an IX lock on the object.

I'm thinking that soon I'll write a post about interpreting some of the other information that shows up in the sys.dm_tran_locks view, for example the entity and the resource_description.  But not today…

Have fun!


Published Thursday, November 19, 2009 5:33 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



Munish Sharma said:

Once again.. very very impressive and straight forward Kaley.. Well done..

April 16, 2013 11:21 PM

obat diabetes said:

August 1, 2018 11:25 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:53 PM

obat sinusitis said:

August 10, 2018 7:26 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:27 AM

obat herbal alami said:

Thank You For Information And Very Useful For Me,Permission to Share

August 30, 2018 7:24 PM

obat benjolan di tubuh said:

August 30, 2018 7:44 PM

obat wasir said:

September 4, 2018 8:30 PM

obat katarak said:

September 16, 2018 5:49 PM

obat maag said:

September 17, 2018 7:14 PM

obat limfoma said:


September 23, 2018 6:54 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement