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: What do you intend with that lock?

Way back in the olden days, prior to SQL Server 7, I already knew that the lock manager was one of the most complex and resource intensive parts of SQL Server. Keeping track of every lock held, who was waiting for that lock, who could be granted the lock, and who was next in line for the data if the lock was released was a lot for SQL Server to keep track of. I admit, I was worried about the promised feature of row-level locking, thinking that it was going to be orders of magnitude more expensive. Without going into all the details of the complete rewrite of the locking implementation for SQL 7, let’s just say that my fears were quite exaggerated.

I’m not saying it’s not expensive to manage locks. It is. But, one of the ways that SQL Server keeps costs down is by being really dumb. If someone has a lock on Resource A, the lock manager will not even test for possible conflicts unless another process requests a lock on the exact same Resource A. What this means, is that if User1 has a lock on MyTable, and then User2 tries to lock a row in MyTable, these are not seen as a possible conflict, as it is two different resources. A table is not the same as a row. But of course, if the lock on MyTable is an exclusive (X) lock, we hope that User2 will not be able to get a lock on a row of MyTable. 

How does SQL Server help us here? It uses something called multigranular locking, which mean locking at multiple levels. To implement multigranular locking, SQL Server takes advantage of a lock mode called Intent Locks. If a process locks a resource that is part of a bigger resource, the containing resource(s) will get Intent Locks. If User3 has an X lock on a row, User3 will also get an Intent-Exclusive (IX) lock on the page that contains the row, and an IX lock on the table.  We can see that in the following example.  First create a table with 1000 rows.

USE tempdb;

            WHERE name = 'Locker')
   DROP TABLE Locker;

(ID int identity,
now datetime,
filler char(100));
INSERT INTO Locker (now, filler)
    SELECT getdate(), REPLICATE(CONVERT(char(30),getdate(), 9), 3);
GO 1000

EXEC sp_spaceused Locker;

Now, update one row in that table in a transaction:

  UPDATE Locker
    SET now = getdate()
  WHERE ID = 99;
-- Do not commit or rollback yet

In another connection, look at the locks:

SELECT resource_type as type, request_status as status,
       request_mode as mode, request_session_id as spid,
    resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 2;

Here are my results:


You can see three locks being held, even thought just one row is locked. The row itself has the X lock (the type for a row lock in a heap is reported as RID), while the page and table have IX locks.

Now, in another connection, try to read the locked row. This query should be blocked:

USE tempdb;
WHERE ID = 99;

Look at the locks again. (I like to sort by spid and type):

SELECT resource_type as type, request_status as status,
       request_mode as mode, request_session_id as spid,
    resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 2
ORDER BY 4, 1;

Here are the results from the second locking query:


You can still see the three locks held by the first connection (spid 52 in my output). There are also three rows for the second connection, spid 55. Note that the blocking is on the row lock, as you can see the status is WAIT, which means the requested lock could not be obtained because of a conflicting lock on the same resource. The second session was able to get the Intent-Shared (IS) on the table (object) and the page, but could not get an S lock on the row. This is because the table and page had IX locks, and IX and IS are compatible. The X lock on the row is NOT compatible with the requested S lock, and you can see the values in the resource column are identical between the two row locks.

If you want to try another example, you could try the first situation I described. First rollback the transaction the was trying to do the update, and make sure there are no locks in tempdb. Then grab an exclusive table lock in the first connection:

  UPDATE Locker with (TABLOCKX)
    SET now = getdate();

And perform the same single row SELECT in the second connection:

WHERE ID = 99;

When you inspect the locks, you should see something like:


We have an X lock on the table, and the second connection blocks as soon as it tries to get the Intent lock on the table, before it ever even tries to get the row lock.  If you want to see the complete chart of what lock modes are compatible with other lock modes, and which will cause blocking, take a lot at the Books Online at:

But remember, the compatibility chart only comes into play for locks on the exact same resource. In the output above, the resource column is empty, but that is because for an object lock, the resource info is available in another column, which I haven't returned here.

Because Intent locks always correspond to (non-intent) locks on lower level resources, you can think of intent locks as SQL Server, or the application,  having the ”Intent" to acquire that lock. If a lock is acquired on a row, there may be an intent to acquire more locks, either on the whole page or on the table itself.

You can have IX and IS as we’ve seen, and also IU locks (Intent-Update). Update locks are very special, and I’ll give them an entire post of their own, probably in my next technical post. However, IU locks can only exist on pages. If a page has an IU lock, the table containing the page has an IX lock.

Hopefully, this scripts and information here will get you started doing some additional exploration of your own.

Have fun!


Published Friday, September 11, 2009 11:56 AM 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



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:

Very impressive post Kalen... I read this concept on many websites even from microsoft... Just in try of knowing each and everything about locking in SQL Server... but your post is simply best... Keep going mate... and many thanks

April 16, 2013 10:05 PM

Michael Durthaler said:

Unlike Munish Sharma :) ... (can't pick on someone I don't know too much ...) I found your blog first.  Sort of felt this would be more informative but in addition to being that (and SIMPLE) it provides tools I've never used before that I can see how and when locks are shared, what happens when COMMIT TRAN or ROLLBACK TRAN is executed, etc.  I ran into the terms on locks in Itzik Ben Gan's book TSQL 2008 (or 2012) Fundamentals.  either flavor covers locks well but only fundamentally.

This post takes the easy next step and let's the end user (or student) play around safely in TempDB and see how this all works.

The watchword almost always is if you love complex and overthought, look on Technet.  If you want to understand, look for posts like this or at least try stack overflow first. :)

Microsoft articles are good but they too often try to make one size fit all.



October 17, 2013 10:42 AM

obat diabetes said:

August 1, 2018 11:25 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:54 PM

obat sinusitis said:

August 10, 2018 7:27 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:28 AM

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

obat maag said:

September 17, 2018 7:15 PM

obat limfoma said:


September 23, 2018 6:54 PM

xiaojun said:

20180928 junda

September 27, 2018 10:45 PM

chenjinyan said:


October 9, 2018 6:53 PM

chenqiuying said:


October 10, 2018 6:52 PM said:"> Golden Goose Super Star, nike free run mens, nike free run shoes,"> adidas Energy Boost 3, nike lunar womens, Converse Store,"> Air Max 90 BR,"> Air VaporMax 20, cheap adidas shoes, adidas Nmd Xr1,"> Golden Goose May, Air Max 90,"> Converse Chuck 70 Low Top,"> adidas Pure Boost Dpr, Air Max 90 Outlet, Yeezy Boost Shoes, adidas Questar Boost, adidas Nmd Runner, Converse Monochrome, Cheap Air Max 90 Shoes, Kyrie 4 Shoes For Sale,"> Air VaporMax Kpu TPU, GGDB Sneakers Sale, adidas Climacool Shoes, Air Max 90 Sale, Golden Goose Sneakers, adidas Yeezy Boost 350, Golden Goose Starter,"> GGDB Francy, Air Max 90 Print, Golden Goose Sneakers,"> Nike Flyknit Trainer, Nike Blazer Shoes, adidas Yeezy Boost 350 V2, Golden Goose Sneakers, philippe model outlet, Kyrie 4 On Sale, Golden Goose Francy,"> Philippe Model Man, adidas Tubular Doom Sock, Golden Goose Ball Star, adidas Nmd Chukka, flyknit lunar 3, Golden Goose Sneakers Outlet, adidas Nmd Shoes, GGDB Sneakers, nike free rn, lunar 2016, nike blazer mid, ggdb shoes,"> yeezy boost 700, Air Max 90 Kpu,"> Golden Goose Slide, adidas Harden Vol 1, GGDB Ball Star, Philippe Model Mens, Philippe Model Sneakers Sale, Air Max 90 Mid, Philippe Model Womens, Golden Goose Superstar,"> Golden Goose Superstar, Air Max 90 Jcrd, adidas Ultra Boost, Golden Goose Slide, Philippe Model Shoes, air max 95 outlet, Golden Goose Ball Star, Nike Air VaporMax Shoes, Air Max 90 Carved, Air Max 90 Sneakerboot, nike free 6.0, converse all star, Air Max 90 Kjcrd Ice Qs, Air Max 90 Mens, adidas Nmd Human Race, adidas Arkyn Boost, adidas Alphabounce, lunarepic flyknit, Cheap Air VaporMax Shoes, adidas Yeezy Boost 700, Air Max 90 Glow, Golden Goose Mid Star, adidas Iniki Runner Boost, nike free advantage, Golden Goose V Star, GGDB Hi Star, lunarglide 7, LeBron 16, adidas Yung 1, Golden Goose Hi Star, air max 270 outlet,"> Golden Goose Hi Star, Air VaporMax 95, adidas Nmd Racer, Converse Low Top, Nike Kyrie 4,"> yeezy powerphase calabasas, air max 270 sale, Air Max 90 QS, air max 95, nike lunar mens, adidas shoes online, yeezy boost 350 v2,"> Golden Goose Francy, adidas Yeezy Boost 330, nike air max 270, adidas Dame 4, Converse All Star Seasonal Color, GGDB Slide, adidas Nmd R2, GGDB Starter, Kyrie 4 Shoes, yeezy boost, Golden Goose Francy, Golden Goose Outlet, converse chuck taylor, Philippe Model Sneakers, Air Max 90 Hyp, adidas Eqt Support, Air VaporMax Plus, Air VaporMax Moc, Yeezy Boost Outlet, Golden Goose Mid Star, nike free run shoes sale, Golden Goose Sneakers, GGDB Mid Star, Converse Shoes, flyknit lunar 2, Converse High Top, Cheap Nike Blazer Shoes, adidas Yeezy Boost 350 V2, nike free tr, Air Max 90 Womens, Nike LeBron 16, nike lunar shoes outlet, Nike LeBron 15, Golden Goose Ball Star, nike flyknit shoes sale, GGDB Francy, Air VaporMax CS, Nike LeBron 16 Shoes, adidas yeezy boost, Air Max 90 Prm, lunarglide 8, Nike Air Max 90, Converse Outlet, lunarglide 5, Golden Goose Sale, Nike Air Max 95 Womens, adidas D Rose 8, Converse Chuck Taylor High Top, Air VaporMax Flyknit, Philippe Model, adidas Nmd R1, nike flyknit shoes, Converse All Star Monochrome, GGDB Superstar, Converse Chuck Taylor Leather, Converse Seasonal Color, Philippe Model Sneakers Outlet, Air Max 90 Shoes, air max 270, adidas Crazy Explosive, nike flyknit shoes outlet, adidas Yeezy Boost, GGDB Slide, ggdb shoes sale, GGDB Hi Star, adidas Yeezy 500, adidas shoes outlet, Air Max 90 EZ, adidas Yeezy Boost 550,"> Golden Goose Starter, Golden Goose Starter, GGDB Starter, Air Max 90 American Flag, nike air max 95, Golden Goose Outlet, adidas Harden Vol 2, cheap air max 270, adidas shoes sale, adidas Nmd City Sock 2 Pk, Golden Goose Superstar, Converse Leather, Philippe Model Sneakers, adidas Climachill Boost, Nike Flyknit Chukka, Nike Blazer Shoes Sale, adidas Crazy Byw, Air VaporMax 97, philippe model outlet online, nike air max 270, Golden Goose, yeezy boost 350, Nike Flyknit Racer, Golden Goose Mid Star, adidas Prophere, Golden Goose Sale, adidas Ultra Boost, LeBron 16 Shoes For Sale, adidas Yeezy Powerphase, golden goose sale, nike lunar on sale, Air Max 90 Fireflies, Converse Chuck Taylor Low Top, adidas Eqt Shoes, Air Max 90 Flyknit, Golden Goose Shoes, Nike Blazer Outlet, lunarglide 4, Nike Kyrie 4, nike free run shoes outlet, Philippe Model Men Sneakers, cheap air max 95, Yeezy Boost, yeezy boost 500, Air Max 90 SP, Golden Goose Hi Star, Golden Goose Sneakers Sale, Air VaporMax Sale, Golden Goose Slide, GGDB Superstar, adidas Pure Boost, adidas shoes, philippe model on sale, adidas Yeezy Boost 650, Air VaporMax, GGDB Ball Star, Philippe Model Sale, converse outlet, Golden Goose Starter, Golden Goose, yeezy boost infant, Golden Goose Slide, Air Max 90 Lthr, golden goose, adidas Yeezy Boost 750, nike free flyknit, Golden Goose Ball Star, adidas Yeezy Boost 750, nike blazer high, nike free 4.0, Golden Goose Mid Star, Philippe Model, Air VaporMax Outlet, adidas Yeezy Boost 700, Golden Goose, adidas I5923, Nike Kyrie 3, adidas Iniki Runner Boost, nike blazer low, cheap nike flyknit shoes, air max 95 sale, nike free 5.0, Philippe Model Woman, nike lunar shoes, Kyrie 4, Golden Goose, LeBron James Jerseys, adidas AW Run, Nike Air Max 95 Mens, air max 95 shoes, yeezy boost 350, GGDB, adidas Nmd Xr4, philippe model sneakers outlet, lunarglide 6, lunar flyknit htm nrg, Air Max 90 Zoom IT, Golden Goose Francy, Air VaporMax Running, Nike LeBron 16, GGDB Mid Star, yeezy boost 350 v2, adidas Dame, adidas Y3 Pure Boost, lunar flyknit chukka, adidas Yeezy Boost 350, Air Max 90 Ultra, Nike LeBron 15, Converse Chuck 70 High Top, nike free run womens, Air Max 90 Essential, Golden Goose Sneakers Sale, Golden Goose Shoes, Philippe Model Women Sneakers, nike fs lite, Golden Goose Sale, Air Max 90 VT, Yeezy Boost Sale, converse shoes, flyknit lunar 1, Converse 1970s

April 12, 2019 3:47 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement