SQL Server Locks

Locks

• Row (RID)
• Key (KEY)
• Page (PAG)
• Extent (EXT)
• Heap or B-tree (HoBT)
• Table (TAB)
• File (FIL)
• Application (APP)
• MetaData (MDT)
• Allocation Unit (AU)
• Database (DB)

Row-Level Lock

查看锁状态：

DROP TABLE IF EXISTS dbo.Test1;
CREATE TABLE dbo.Test1 (C1 INT);
INSERT INTO dbo.Test1
VALUES (1);
GO

BEGIN TRAN
DELETE dbo.Test1
WHERE C1 = 3;
SELECT dtl.request_session_id,
 dtl.resource_database_id,
 dtl.resource_associated_entity_id,
 dtl.resource_type,
 dtl.resource_description,
 dtl.request_mode,
 dtl.request_status
FROM sys.dm_tran_locks AS dtl
WHERE dtl.request_session_id = @@SPID;
ROLLBACK

• 第三行的RID是1:336:2，行RID从0开始

• 通过视图 sys.dm_tran_locks 来查看锁状态

• FileID:PageID:Slot(row)

• FileID 为1代表是primary file

• OBJECT代表Table

• resource_database_id是数据库id

查看表名及数据库信息：

SELECT OBJECT_NAME(613577224), DB_NAME(11);

Key-Level Lock

This is a row lock within an index, and it is identified as a KEY lock.

Page-Level Lock

• FileID:PageID

页锁锁的是表或索引上的一个单独的页。

当一个查询请求了多行时，它可以获取多个行锁或是一个页锁，这取决于lock manager对于获取多个行锁的压力的评判，如果它认为压力太大，那它就获取一个页锁而不是多个行锁。

页锁可以提高性能（相比获取多个行锁），但伤害并发能力。

Extent-Level Lock

一个Extent代表8个连续的页，见数据结构一节中的图。

这个锁在表上执行ALTER INDEX REBUILD命令时被用到，页或从一个已存在的extent移动到新的extent，此时使用EXT锁保证extent的一致性。

Heap or B-tree Lock

这个锁作用的对象可以是unordered heap table，无聚集索引的table，B树。

It acts like a table-level lock but on a partition instead of on the table itself.

Table-Level Lock

OBJECT

• 表级锁保留整个表及其索引。

• 由lock manager根据压力来决定是申请行锁、页锁还是直接申请表锁。

• SELECT * FROM <TableName> WITH(TABLOCK) 可以直接申请表锁，但不建议这样。

DB-Level Lock

当应用使用一个数据库连接时，lock manager会以session_id为单位为其分配一个DB共享锁，这个锁防止当有其它连接存在时用户drop或restore数据库。

SQL Server保证查询只能同时获取一种级别的锁。如果查询获取了一个行锁，那么另一个查询不能获取任何可能影响到这行的完整性（integrity）的其它级别的锁（无论比这锁高级还是低级），其它查询可以获取其它的行锁或者页锁，但不能获取到包含这行的相关的锁。

锁的等级由lock manager自动分配，而不需要用户或者管理员指定。

为了提高并发效率，lock manager一般倾向于row-level lock或key-level lock，但当获取这些锁效率相对低时，它也会自动获取更高级别的锁。

引用

1. 《SQL Server 2017 SQL Performance Tunning 5th Edition》