Is HOLDLOCK Really the Same as SERIALIZABLE?

A question came up recently about the HOLDLOCK table hint in Azure SQL Database / SQL Server. The documentation states rather simply that it is equivalent to SERIALIZABLE. So is it really? Fortunately this is easy for us to test.

The Test

Let's start off by creating a numbers table in the test database of our choosing.

1SELECT TOP (10000) IDENTITY(INT, 1, 1) AS n
2INTO dbo.Numbers
3FROM sys.objects o
4CROSS JOIN sys.objects o2;

From there, we will create an index, and then open a transaction and select a range of numbers using the HOLDLOCK table hint.

1CREATE UNIQUE INDEX UQ_Number
2ON dbo.Numbers (n);
3
4BEGIN TRAN
5
6SELECT n
7FROM dbo.Numbers WITH (HOLDLOCK)
8WHERE n >= 1010
9AND n < 1018;

We now have a SELECT query that has executed within a transaction that is still open. Given how the SERIALIZABLE transaction isolation level operates, if it is indeed being used we should see key range locks if we query sys.dm_tran_locks.

1SELECT request_mode, resource_description, request_type, request_status
2FROM sys.dm_tran_locks
3WHERE request_session_id = @@spid
4AND resource_type = 'KEY';

The results of this query are:

As we can see, range locks are indeed being held. Again, range locks are only used by the SERIALIZABLE isolation level. I would also like to point out that while our query above selected 8 rows (numbers 1010 through 1017), there are 9 range locks being held - one for each row returned, and one extra at the end to prevent any more rows from being inserted into the range.

Now that we're all done, let's not forget to rollback that open transaction we created!

1ROLLBACK;

Results

As we have now observed for ourselves, the HOLDLOCK table hint really does use the SERIALIZABLE isolation level, just as the documentation states.