Agenda

前文
兩種圍度的Lock
- Lock範圍
- Lock類型
  - Update Lock 存在的意義
Lock 互斥 Demo
- Locking Optimization
- NoLock的隱憂
  - Read Uncommitted 髒讀取
小結
前文

之前有跟大家介紹資料庫交易中的ACID,今天我們就來談談常常聽到Lock

在討論Lock前我們必須先了解,為什麼會有Lock?

假如你的系統能保證只有一個使用著操作每個資源,其實也就不用lock存在,但現實生活中往往有個命令對於同一個資源操作.這時候我們為了確保資料正確性,必須使用lock來避免Racing Condition.

在早期系統我們要儲存資料會存放檔案在Disk並使用類似Excel方式來儲存,但這會導致每次讀取只有有一個使用者(因為對於檔案上Lock),被lock資源其他人就無法存入

兩種圍度的Lock

在Sql-Server Lock有分兩種圍度

Lock範圍
Lock類型

Lock範圍

Sql-Server支援我們在同一時間能建立不同交易執行命令
是因為Sql-Server有許多不一樣力度範圍Lock.

下表表示鎖範圍等級由上到下越來越大.

Key (•KEY)
Page (•PAG)
Extent (•EXT)
Heap or B-tree (•HoBT)
Table (•TAB)
File (•FIL)
Application (•APP)
MetaData (•MDT)
Allocation Unit (•AU)
Database (•DB)yment_document = ‘Journal Entry’

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 產生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 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 dbo.T2 WITH(XLOCK) 
WHERE Id = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

我們馬上開另一個Session,執行查詢ID=1語法

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

會發現我們需要等上面語法執行完才能查出資料，那是因為 X Lock 跟X Lock會互斥我們，必須等到XLock執行完我們才可以得到資料.

Locking Optimization

如果沒有髒讀取且是 Row 的 ShardLock，SQL-Server 會有個優化動作不對於此 ROW 上 SharedLock

SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in the right circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.

我們一樣可以開兩個視窗來跑下面語法，會發現 Session2 並不會被 Block

Session1

BEGIN TRAN

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

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

Session2

SELECT *
FROM dbo.T2 
WHERE Id = 1

但如果是使用 PageLock 就會導致 Session2 被 block，因為 Session2 會嘗試上 IX Lock 在 Page 上，但 Session1 已經把 Page 上 XLOCK (IX Lock 和 XLOCK 互斥)

Session1

BEGIN TRAN

SELECT * 
FROM dbo.T2 WITH(XLOCK,PAGLOCK) 
WHERE Id = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

Session2

SELECT *
FROM dbo.T2 
WHERE Id = 1

參考資料：read-committed-shared-locks-and-rollbacks

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做了基本介紹

Lock範圍
Lock類型

with(nolock)記得要慎用,他會造成資料讀取上有誤差,建議在高併發系統且交易有關程式碼,請別使用with(nolock),這會造成資料不正確(有資料執行到一半RollBack,剛好被NoLock查詢讀到)

日後有機會再慢慢介紹更多Lock運用時間和注意事項.

Transaction Locking and Row Versioning Guide

此文作者：Daniel Shih(石頭)
此文地址： https://isdaniel.github.io/DBLock-1/
版權聲明：本博客所有文章除特別聲明外，均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處！

Hello World

淺談SqlServer Lock(一)