浅谈SqlServer Lock(一)

• 前文
• 两种围度的Lock
  • Lock范围
  • Lock类型
    • Update Lock 存在的意义
• Lock互斥Demo
  • NoLock的隐忧
• 小结

前文

之前有跟大家介绍资料库交易中的ACID,今天我们就来谈谈常常听到Lock

在讨论Lock前我们必须先了解,为什麽会有Lock?

假如你的系统能保证只有一个使用着操作每个资源,其实也就不用lock存在,但现实生活中往往有个命令对於同一个资源操作.这时候我们为了确保资料正确性,必须使用lock来避免Racing Condition.

在早期系统我们要储存资料会存放档案在Disk并使用类似Excel方式来储存,但这会导致每次读取只有有一个使用者(因为对於档案上Lock),被lock资源其他人就无法存入

此文章同步发布石头blog

两种围度的Lock

在Sql-Server Lock有分两种围度

1. Lock范围
2. Lock类型

Lock范围

Sql-Server支援我们在同一时间能建立不同交易执行命令
是因为Sql-Server有许多不一样力度范围Lock.

下表表示锁范围等级由上到下越来越大.

• 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)

Lock类型

在SqlServer有许多类型Lock

• Shared Locks (s)
• Update Locks (U)
• Exclusive Locks (X)
• Intent Locks (I)
• Schema Locks (Sch)
• Bulk Update Locks (BU)
• Key-range

下表是Lock类型互斥或相容对应表

例如:你在使用查询(Shared Lock),除了上XLock资源外其余资料都可同步被查找出来.

Update Lock 存在的意义

我们在更新资料时使用Lock类型会如下

Shared Lock => Update Lock => XLock

• Shared Lock:查询更新的资料.
• Update Lock:更新前把资料改成Update Lock.
• XLock:确定要更新当下改成XLock.

但为什麽会多一个Update Lock呢?

因为可以避免DeadLock产生机率.

假如有一个Update语法同时被执行.

Update T
Set Val = @Val
Where id = 1

如果只有Shared Lock => XLock

1. 语法1 产生Shared Lock
2. 语法2 产生Shared Lock
3. 因为Shared Lock 和 XLock 互斥,所以互相等待对方Shared Lock释放，造成死结(Dead Lock)

假如我们多一个ULock会变成

1. 语法1 产生Shared Lock
2. 语法2 产生Shared Lock
3. 语法1 产生ULock(释放Shared Lock)
4. 语法2 想要产生ULock发现语法1已经先产生(ULock)，所以等待语法1执行完毕(Block)
5. 语法1 Update完後产生XLock直到Commit结束才释放XLock
6. 语法2 产生ULock执行後面更新动作.

Shared Lock执行完查询後立即释放资源
关键在於Shared Lcok不互斥,ULock互斥

Lock互斥Demo

我们建立一张T2资料表

DROP TABLE IF EXISTS T2

CREATE TABLE T2 (Id int)

INSERT INTO T2 VALUES (1)
INSERT INTO T2 VALUES (2)

在使用Transaction + XLOCK hint在查询语法(这时T2查询的资料就会被上XLock了)

BEGIN TRAN

SELECT * 
FROM T2 WITH(XLOCK) 
WHERE Id = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

我们马上开另一个Session,执行查询ID=1语法

SELECT *
FROM dbo.T2 
WHERE Id = 1

会发现我们需要等上面语法执行完才能查出资料,那是因为我们Shared Lock跟X Lock会互斥我们,必须等到XLock执行完我们才可以得到资料.

NoLock的隐忧

上文有提到Shard Lock会被XLock给Block住,如果我非得在资料上XLock时查询资料有办法吗?

有,我们在第二句查询加上With(Nolock)hint或者是(设定SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)不然Shard Lock会被XLock给Block住.

但使用With(Nolock) Read Uncommitted要慎用,因为是脏读取,(Read Uncommitted顾名思义就是读取未commite资料)

Read Uncommitted 脏读取

我们试着把上面范例稍微修改一下第一个查询语法

BEGIN TRAN

UPDATE dbo.T2
Set id = 100
where id = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

第二个查询语法

SELECT *
FROM dbo.T2 with(nolock)
WHERE Id = 100

在资料上XLock时使用with(nolock)来查询资料,会发现可以查询出Id=100资讯

但因为第一句语法因为一些原因RollBack,过段时间再查询

我们会得到空的结果集...那是因为with(nolock)是脏读取,在查询时他会直接拿取目前资料最新状态(这个资料状态可能不一定,最後结果),假如RollBack就会导致资料错误问题.

有时候NoLock会读到重复资料
所以建议在跟算钱或交易有关程序码,请别使用with(nolock)

小结

本篇对於Lock做了基本介绍

1. Lock范围
2. Lock类型

with(nolock)记得要慎用,他会造成资料读取上有误差,建议在高并发系统且交易有关程序码,请别使用with(nolock),这会造成资料不正确(有资料执行到一半RollBack,刚好被NoLock查询读到)

日後有机会再慢慢介绍更多Lock运用时间和注意事项.

Transaction Locking and Row Versioning Guide