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:

Blog image 1.png

 

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:

Blog image 2.png

 

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:

Blog image 3.png

 

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. Here is a charge of the basic lock modes and which are compatible with which:

Blog image 4.png

If you want to see the complete chart of what lock modes are compatible with other lock modes, and which will cause blocking, as well as a lot of other locking details, take a look at the this page in the documentation.

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, the scripts and information here will get you started doing some additional exploration of your own.

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