Sql-server – Why use an intent lock at a higher level in the hierarchy, why not just a regular lock

blockingconcurrencylockingsql-server-2016

This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need to check every row for active locks if it wants to place a lock on a page or table.

As an example, if I update a single row in AdventureWorks:

USE AdventureWorks2014;

BEGIN TRAN;
UPDATE  Person.Person 
SET     LastName = 'Smith' 
WHERE   BusinessEntityID = 1;

and then in another session, run sp_whoisactive with get_locks to see what locks were taken:

EXEC sp_whoisactive @get_locks = 1;

I can see the following:

      <Locks>
        <Lock resource_type="KEY" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="KEY" index_name="PK_Person_BusinessEntityID" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="IX" request_status="GRANT" request_count="4" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK_Person_BusinessEntityID" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>

this backs up what the article above says – we have an exclusive row (key) lock on the two relevant indexes and therefore the pages and object have IX locks.

If I try and SELECT that row in another session:

SELECT * FROM Person.Person WHERE BusinessEntityID = 1

that query is indeed blocked.

My question therefore is, why is an intent lock a separate thing to a "regular" lock? Why would SQL Server not just place an X lock on the table?

Best Answer

I think I have worked out the answer (Please correct this if my understanding is wrong)

After a bit more reading, I found a simple matrix here:

enter image description here

It appears that the X lock is less compatible with other locks than the the IX lock.

If we were to put an X lock on a table due to reading one row, we couldn't get an IS lock on that table but if we have an IX lock on the table we can get a IS lock.

If I understand correctly then, If we were to lock the table with an X lock to update one row, we couldn't read a different row because the read couldn't place an IS on the table. However, if we use an IX, it can get the IS lock it needs.

The following test seems to prove this is the case:

Update the single row:

USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person 
SET     LastName = 'Smith' 
WHERE   BusinessEntityID = 1;

in another session, SELECT a different row to the one being updated

SELECT * FROM Person.Person WHERE BusinessEntityID = 2

this row returns without being blocked

However, if I was to gain an X lock:

USE AdventureWorks2016;

BEGIN TRAN;
UPDATE  Person.Person WITH (TABLOCKX)
SET     LastName = 'Smith' 
WHERE   BusinessEntityID = 1;

this query is now blocked:

SELECT * FROM Person.Person WHERE BusinessEntityID = 2
Related Question