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][1]. I can't find anything in the docs either for the "locks entire table": [SET TRANSACTION ISOLATION LEVEL][2]. 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)? [1]: https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level [2]: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017
TopAnswers
Databases
Top Answer
meme (imported from SE)
Escalation, though
--
[Lock escalation][1] 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.
[1]: https://dba.stackexchange.com/questions/12864/what-is-lock-escalation