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

IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Locker')
   DROP TABLE Locker;
GO

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

EXEC sp_spaceused Locker;
GO

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

BEGIN TRAN
  UPDATE Locker
    SET now = getdate()
  WHERE ID = 99;
GO
-- 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:

image

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;
GO
SELECT * FROM Locker
WHERE ID = 99;
GO

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;
GO

Here are the results from the second locking query:

image

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:

BEGIN TRAN
  UPDATE Locker with (TABLOCKX)
    SET now = getdate();
GO

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

SELECT * FROM Locker
WHERE ID = 99;
GO

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

image

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:

http://msdn.microsoft.com/en-us/library/452559b2-c536-43ec-a2da-a558abfa8a32.aspx

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!

~Kalen

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

Comments

 

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.

Thanks!

Mike

October 17, 2013 10:42 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