浅谈SqlServer Lock(一)

前文两种围度的LockLock範围Lock类型Update Lock 存在的意义Lock互斥DemoNoLock的隐忧小结

前文

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

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

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

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

此文章同步发布石头blog

两种围度的Lock

在Sql-Server Lock有分两种围度

Lock範围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 TSet Val = @ValWhere id = 1

如果只有Shared Lock => XLock

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

假如我们多一个ULock会变成

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

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

Lock互斥Demo

我们建立一张T2资料表

DROP TABLE IF EXISTS T2CREATE TABLE T2 (Id int)INSERT INTO T2 VALUES (1)INSERT INTO T2 VALUES (2)

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

BEGIN TRANSELECT * FROM T2 WITH(XLOCK) WHERE Id = 1WAITFOR 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 TRANUPDATE dbo.T2Set id = 100where id = 1WAITFOR 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做了基本介绍

Lock範围Lock类型

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

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

Transaction Locking and Row Versioning Guide