sql-server add tag
mslot (imported from SE)
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
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.