Does SQL Server's serializable isolation level lock entire table

Me and a colleague of mine discussed the implications of use of the serializable isolation level. He said it locked the entire table, but I disagreed to that telling him it potentially could but it tries to apply range locks and it doesn't apply true serialization as explained here: The Serializable Isolation Level.

I can't find anything in the docs either for the "locks entire table": SET TRANSACTION ISOLATION LEVEL.

The doc states a bunch of things regarding range locks, so in theory you could lock the entire table by simply having a range lock that locks the entire range of possible values in the table, but it doesn't lock the table.

Am I completely wrong here? Does it in fact lock the entire table (or tables)?

Escalation, though

Lock escalation under serializable isolation level may occur the same as it does with other isolation levels.

• Correct indexes can help to avoid lock escalation up to a point

• Locking many indexes will increase the likelihood of lock escalation; the count is cumulative across objects for a single statement

Some quick examples using a single table with a single index. Id is the primary key and clustered index on the table.

One Row

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647 
FROM dbo.Comments AS c
WHERE c.Id = 138; --One value

ROLLBACK

For a single Id value, locking is minimal.

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |           1 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |           1 |
+--------------+---------------+---------------+-------------+

Multiple Rows

But locks will go up if we start working in ranges:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647 
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000; -- Small range

ROLLBACK

Now we have more exclusive locks on more keys:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

Way More Rows

This will carry on until we hit a tipping point:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647 
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 11655; --Larger range

ROLLBACK

Lock escalation is attempted and is successful:

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| X            | Comments      | OBJECT        |           1 |
+--------------+---------------+---------------+-------------+

Pay Attention

It's important to separate two concepts here: the isolation level will be serializable no matter what kind of locks are taken. The query chooses the isolation level, and the storage engine chooses the locks. Serializable won't always result in range locks -- the storage engine can pick whichever kind of locks still honor the isolation level.

StackExchange Database Administrators Q#249838, answer score: 23