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

image

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!

~Kalen

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

Comments

 

Munish Sharma said:

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

April 16, 2013 11:21 PM

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