UPDLOCK and HOLDLOCK query not creating the expected lock

Question

I have the below table:

CREATE TABLE [dbo].[table1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I'm learning how SQL locks work, and I'm trying to test a situation where I want to lock a row from being read and updated. Some of the inspiration in this quest starting from this article, and here's the original problem I was trying to solve.

When I run this T-SQL:

BEGIN TRANSACTION

SELECT * FROM dbo.table1 WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION

I would expect an exclusive lock to be placed on the table, and specifically for the row (if I had a WHERE statement on the primary key)

But running this query, I can see that the GRANTed LOCK is for the request mode IX.

SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');

Also, in seperate SSMS windows, I can fully query the table while the transaction is running.

Why is MSSQL not respecting the lock hints?

(SQL Server 2016)

Edit 1
Any information about how these locks work is appreciated, however, the issue at hand is that SQL Server does not seem to be enforcing the locks I'm specifying. My hunch is that this has to do with row versioning, or something related.

Edit 2
I created this Github gist. It requires .NET and the external library Dapper to run (available via Nuget package).

Here's the interesting thing I noticed:

• SELECT statements can be ran against table1 even though a previous query with UPDLOCK, HOLDLOCK has been requested.
• INSERT statements cannot be ran while the lock is there
• UPDATE statements against existing records cannot be ran while the lock is there
• UPDATE statements against non-existing records can be ran.

Here's the Console output of that Gist:

Run locking SELECT Start - 00:00:00.0165118
Run NON-locking SELECT Start - 00:00:02.0155787
Run NON-locking SELECT Finished - 00:00:02.0222536
Run INSERT Start - 00:00:04.0156334
Run UPDATE ALL Start - 00:00:06.0259382
Run UPDATE EXISTING Start - 00:00:08.0216868
Run UPDATE NON-EXISTING Start - 00:00:10.0236223
Run UPDATE NON-EXISTING Finished - 00:00:10.0268826
Run locking SELECT Finished - 00:00:31.3204120
Run INSERT Finished - 00:00:31.3209670
Run UPDATE ALL Finished - 00:00:31.3213625
Run UPDATE EXISTING Finished - 00:00:31.3219371

Answer 1

and I'm trying to test a situation where I want to lock a row from being read and updated

If you want to lock a row from being read and updated you need an exclusive lock, but UPDLOCK lock hint requests update locks, not exclusive locks. The query should be:

SELECT * FROM table1 WITH (XLOCK, HOLDLOCK, ROWLOCK)
WHERE Id = <some id>

Additionally, under READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels, SELECT statements don't request shared locks, just schema stability locks. Therefore, the SELECT statement can read the row despite there is an exclusive lock. And surprisingly, under READ COMMITTED isolation level, SELECT statements might not request row level shared locks. You will need to add a query hint to the SELECT statement to prevent it from read the locked row:

SELECT * FROM dbo.Table1 WITH (REPEATABLEREAD)
WHERE id = <some id>

With REPEATABLEREAD lock hint, the SELECT statement will request shared locks and will hold them during the transaction, so it won't read exclusively locked rows. Note that using READCOMMITTEDLOCK is not enough, since SQL Server might not request shared locks under some circumstances as described in this blog post.

Please, take a look at the Lock Compatibility Table

Under the default isolation level READ COMMITTED, and with not lock hints, SELECT statements request shared locks for each row it reads, and those locks are released immediately after the row is read. However, if you use WITH (HOLDLOCK), the shared locks are held until the transaction ends. Taking into account the lock compatibility table, a SELECT statement running under READ COMMITTED, can read any row that is not locked exclusively (IX, SIX, X locks). Exclusive locks are requested by INSERT, UPDATE and DELETE statements or by SELECT statements with XLOCK hints.

I would expect an exclusive lock to be placed on the table, and specifically for the row (if I had a WHERE statement on the primary key)

I need to understand WHY SQL Server is not respcting the locking directives given to it. (i.e. Why is an exclusive lock not on the table, or row for that matter?)

UPDLOCK hint doesn't request exclusive locks, it requests update locks. Additionally, the lock can be granted on other resources than the row itself, it can be granted on the table, data pages, index pages, and index keys. The complete list of resource types SQL Server can lock is: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, and ALLOCATION_UNIT. When ROWLOCK hint is specified, SQL Server will lock rows, not pages nor extents nor tables and the actual resources that SQL Server will lock are RID's and KEY's

Answer 2

@Remus Rusuanu has explained it a lot better than I ever could here.

In essence - you can always read UNLESS you ask for the same lock type (or more restrictive). However, if you want to UPDATE or DELETE then you will be blocked. But as I said, the link above explains it really well.

Answer 3

Your answer is right in the documentation:

https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

This is why you are getting an index lock (IX) and not a table row lock.

And this explains why you can read while running the first query:

http://aboutsqlserver.com/2011/04/14/locking-in-microsoft-sql-server-part-1-lock-types/

Update locks (U). Those locks are the mix between shared and exclusive locks. SQL Server uses them with data modification statements while searching for the rows need to be modified. For example, if you issue the statement like: “update MyTable set Column1 = 0 where Column1 is null” SQL Server acquires update lock for every row it processes while searching for Column1 is null. When eligible row found, SQL Server converts (U) lock to (X).

Your UPDLock is an update lock. Notice that update locks are SHARED while searching, and changed EXCLUSIVE when performing the actual update. Since your query is a select with an update lock hint, the lock is a SHARED lock. This will allow other queries to also read the rows.