主页 / dba / 问题 / 249838
Accepted
mslot
Asked: 2019-09-28 22:57:53 +0800 CST

SQL Server 的可序列化隔离级别是否锁定整个表

  • 772

我和我的一位同事讨论了使用可序列化隔离级别的含义。他说它锁定了整个表，但我不同意告诉他它可能会，但它会尝试应用范围锁并且它不应用真正的序列化，如此处所述：可序列化隔离级别。

我在文档中也找不到“锁定整个表”的任何内容：SET TRANSACTION ISOLATION LEVEL。

该文档说明了一系列有关范围锁的内容，因此理论上您可以通过简单地使用范围锁来锁定整个表，该范围锁可以锁定表中可能值的整个范围，但它不会锁定表。

我在这里完全错了吗？它实际上是否锁定了整个表（或表）？

sql-server isolation-level

2 个回答

  1. Best Answer

    升级，虽然

    可序列化隔离级别下的锁升级可能与其他隔离级别相同。

    • 正确的索引可以帮助避免锁定升级到某个点
    • 锁定许多索引会增加锁升级的可能性；对于单个语句，计数是跨对象累积的

    使用带有单个索引的单个表的一些快速示例。Id 是表的主键和聚集索引。

    一排

    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

    对于单个 Id 值，锁定是最小的。

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

    多行

    但是如果我们开始在范围内工作，锁会增加：

    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

    现在我们在更多的键上有更多的独占锁：

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

    更多行

    这将一直持续到我们达到临界点：

    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

    尝试锁升级并成功：

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

    注意

    在这里区分两个概念很重要：无论采用哪种锁，隔离级别都是可序列化的。查询选择隔离级别，存储引擎选择锁。可序列化并不总是导致范围锁——存储引擎可以选择任何一种仍然符合隔离级别的锁。

    • 20

  2. 如果搜索谓词上有索引，它可能用于范围锁。

    即，在范围内从第一行锁定到下一行。从下一个到第三行，等等。直到范围中的最后一行。所以本质上是一些行锁，但它也阻止了插入范围的“中间”值（锁定范围）。

    为此，您（SQL Server）需要使用索引。如果没有用于锁定的索引（谓词上的索引），您将（据我所知）将获得表锁。

    • 6

相关问题