Bsrgvty

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.

If there is an index on a search predicate, it might be used for range locks.

I.e., lock from first row to next in range. And from that next to third row, etc. Up to the last row in the range. So essentially a number of row locks, but it blocks the range from inserts also for "in-between" values (locking the range).

For this to happen you (SQL Server) need to have an index to work with. Without indexes to do the locking in (indexes on predicates), you'll (from what I know) will get table locks.