SQL Server锁模式与兼容性

锁的模式

• Shared (S)

• Update (U)

• Exclusive (X)

• Intent

• Intent Shared (IS)

• Intent Exclusive (IX)

• Shared with Intent Exclusive (SIX)

• Schema

  • Schema Modification (Sch-M)
  • Schema Stability (Sch-S)

• Bulk Update (BU)

• Key-Range

锁的升级

• 锁的升级由SQL Server在运行事务的过程中决定

• 如果事务超过了threshold，行锁和页锁会自动升级为表锁

• 当锁升级为表锁后，其它低等级的锁会自动释放

Shared

SELECT

• S锁被持有时，其它事务无法修改数据。

• S锁被持有时，其它事务可以读取数据。

• 默认情况（ read_committed隔离级别）下S锁在数据读取完毕后立即释放，如下，S锁不会持有到事务结束，而是在SELECT完成后就释放了。

BEGIN TRAN 
    SELECT * 
    FROM Production.Product AS p 
    WHERE p.ProductID = 1; 
--Other queries 
COMMIT
Update
UPDATE

• U锁与S锁及IS锁兼容，与其它锁不兼容。

• UPDATE语句实际包含两步：

  • 读需要被修改的数据；然后修改
  • 首先获得U锁，然后U锁会被转换成X锁用于修改
  • 分两步的目的是为了在U锁和S锁兼容的情况下提高并发性能，直到真正开始修改时才获得X锁

• 如果修改没有发生，那么U锁会被释放，并不会被持有到事务结束。

• 为什么在第一步不用S锁替代U锁：

BEGIN TRAN 
--1.Read data to be modified using (S)lock instead of (U)lock. 
-- Retain the (S)lock using REPEATABLEREAD locking hint, since 
-- the original (U)lock is retained until the conversion to 
-- (X)lock. 
SELECT * 
FROM Sales.Currency AS c WITH (REPEATABLEREAD) 
WHERE c.CurrencyCode = 'EUR' ; 
--Allow another equivalent update action to start concurrently 
WAITFOR DELAY '00:00:10' ; 
--2. Modify the data by acquiring (X)lock 
UPDATE Sales.Currency WITH (XLOCK) 
SET Name = 'EURO' 
WHERE CurrencyCode = 'EUR' ; 
COMMIT

• 在 REPEATABLEREAD 隔离级别下模仿U锁直到更新完成才释放的行为。

• 如果使用S锁替代U锁，那么两个同时执行的以上事务将造成死锁：两个事务都持有S锁，都想升级X锁，都等待对方释放。（根本原因在于需要资源互斥而S锁不互斥，导致满足了死锁的四个条件：（排他锁）资源互斥、（请求X锁保持S锁）请求与保持、（S锁）不剥夺、（S锁）循环等待，从而造成死锁。

• 不同于S锁，U锁在同一时刻不允许其它U锁锁定同一资源，没有请求与保持及循环等待的情况，打破了死锁条件。

Exclusive

INSERT, UPDATE, DELETE

• 排他锁：其它的读、写操作都无法访问（除非是NOLOCK或读未提交隔离级别）

  • 在 SQL Server 中，如果一个事务需要获取某个数据资源的排他锁（X 锁），则必须等待其他已经持有该资源上的共享锁（S 锁）、排他锁（X 锁）全部释放之后，才能成功加锁。

• INSERT和DELETE语句都在一开始就获取X锁。

• X锁被持有直到事务结束

• X锁的作用（目的):

  • 保证资源的一致性，不让其它事务看到中间状态，其他事务读到的数据要么在修改之前要么在修改之后
  • 因为X锁的排他性，因此事务可以安全地rollback

Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) Modes

• IX锁在表上，暗示事务想要在page、row或key上获得X锁。

• IX锁在页上，暗示事务想要在行上获得X锁。

• IX锁在比表还高的级别上，避免其它事务在表和页上获取不兼容的锁。

• 一个事务在higher level上标记IX/IS锁，并持有lower level的锁，能够避免其他事务在higher level上获得不兼容的锁。

• 意向锁被一个事务持有直到事务结束。

• 意向锁有两个目的：

  • 保护低等级的锁不被高等级的锁覆盖
  • 提高高等级上锁的检测效率（假如一个行锁被一个事务持有，另一个事务想请求page锁，由于I锁的存在，它无需扫描整个page来确认是否有锁被其它事务持有）

Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes

Schema Modification (Sch-M) 模式和 Schema Stability (Sch-S) 模式是数据库中的两种锁模式，用于管理对数据库对象（如表、视图、存储过程等）的结构修改和稳定性控制。

Bulk Update (BU) Mode

The Bulk Update lock mode is unique to bulk load operations. These operations are the older-style bcp (bulk copy), the BULK INSERT statement, and inserts from the OPENROWSET using the BULK option.

Key-Range Mode

• 隔离级别要求Serializable

• Key-Range锁会被应用在范围的key上

• 锁定一个范围避免其它行插进来

• Key-Range锁必须配合索引使用

锁的兼容性

• 如果资源已被一个事务锁定，那么在锁不兼容的情况下，新的锁请求将等待锁释放或自己的锁请求超时。

• IX锁和IX锁兼容是因为，一个X只更新某些行（但如果它们更新的是相同的行，那么其中一个事务仍然要等待另一个事务释放X锁）

引用

1. Lock compatibility
2. 《SQL Server 2017 Query Performance Tuning 5th Edition》