Geek City: Intent to Update

In the last couple of posts, 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, with a couple of extra callouts for emphasis:

figure 1.png

 

Session 64 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

P.S. And for LOTS more details on how to deal with and troubleshoot Locking and Blocking, I'm offering a half day class on June 14! 

https://www.sqlserverinternals.com/events/2018/3/28/online-class-sql-server-concurrency-control