SqlServer中的鎖

一. 為什麼要引入鎖

多個用戶同時對資料庫的併發操作時會帶來以下數據不一致的問題。

二、鎖的分類

（1） 從資料庫系統的角度來看

鎖分為以下三種類型：
* 獨占鎖（Exclusive Lock）（排它鎖）
獨占鎖鎖定的資源只允許進行鎖定操作的程式使用，其它任何對它的操作均不會被接受。執行數據更新命令，即INSERT、 UPDATE 或DELETE 命令時，SQL Server 會自動使用獨占鎖。但當對象上有其它鎖存在時，無法對其加獨占鎖。獨占鎖一直到事務結束才能被釋放。
* 共用鎖（Shared Lock）
共用鎖鎖定的資源可以被其它用戶讀取，但其它用戶不能修改它。在SELECT 命令執行時，SQL Server 通常會對對象進行共用鎖鎖定。通常加共用鎖的數據頁被讀取完畢後，共用鎖就會立即被釋放。
* 更新鎖（Update Lock）
更新鎖是為了防止死鎖而設立的。當SQL Server 準備更新數據時，它首先對數據對象作更新鎖鎖定，這樣數據將不能被修改，但可以讀取。等到SQL Server 確定要進行更新數據操作時，它會自動將更新鎖換為獨占鎖。但當對象上有其它鎖存在時，無法對其作更新鎖鎖定。

（2）從程式員的角度看

鎖分為以下兩種類型：
* 樂觀鎖（Optimistic Lock）
樂觀鎖假定在處理數據時，不需要在應用程式的代碼中做任何事情就可以直接在記錄上加鎖、即完全依靠資料庫來管理鎖的工作。一般情況下，當執行事務處理時SQL Server會自動對事務處理範圍內更新到的表做鎖定。
* 悲觀鎖（Pessimistic Lock）
悲觀鎖對資料庫系統的自動管理不感冒，需要程式員直接管理數據或對象上的加鎖處理，並負責獲取、共用和放棄正在使用的數據上的任何鎖。

（3）從鎖的粒度看

鎖是加在資料庫對象上的。而資料庫對象是有粒度的，比如同樣是1這個單位，1行，1頁，1個B樹，1張表所含的數據完全不是一個粒度的。因此，所謂鎖的粒度，是鎖所在資源的粒度。

Microsoft SQL Server 資料庫引擎具有多粒度鎖定，允許一個事務鎖定不同類型的資源。 為了儘量減少鎖定的開銷，資料庫引擎自動將資源鎖定在適合任務的級別。 鎖定在較小的粒度（例如行）可以提高併發度，但開銷較高，因為如果鎖定了許多行，則需要持有更多的鎖。 鎖定在較大的粒度（例如表）會降低了併發度，因為鎖定整個表限制了其他事務對錶中任意部分的訪問。 但其開銷較低，因為需要維護的鎖較少。

三、sqlserver提供的表級鎖

sqlserver所指定的表級鎖定提示有如下幾種 

1． HOLDLOCK: 在該表上保持共用鎖，直到整個事務結束，而不是在語句執行完立即釋放所添加的鎖。

2． NOLOCK：不添加共用鎖和排它鎖，當這個選項生效後，可能讀到未提交讀的數據或“臟數據”，這個選項僅僅應用於SELECT語句。

3． PAGLOCK：指定添加頁鎖（否則通常可能添加表鎖）

4． READCOMMITTED用與運行在提交讀隔離級別的事務相同的鎖語義執行掃描。預設情況下，SQL Server 2000 在此隔離級別上操作。

5． READPAST: 跳過已經加鎖的數據行，這個選項將使事務讀取數據時跳過那些已經被其他事務鎖定的數據行，而不是阻塞直到其他事務釋放鎖，READPAST僅僅應用於READ COMMITTED隔離性級別下事務操作中的SELECT語句操作

6． READUNCOMMITTED：等同於NOLOCK。

7． REPEATABLEREAD：設置事務為可重覆讀隔離性級別。

8． ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。

9． SERIALIZABLE：用與運行在可串列讀隔離級別的事務相同的鎖語義執行掃描。等同於 HOLDLOCK。

10． TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執行完後釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務結束。

11． TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務讀或更新這個表的數據，直到這個語句或整個事務結束。

12． UPDLOCK ：指定在讀表中數據時設置更新鎖（update lock）而不是設置共用鎖，該鎖一直保持到這個語句或整個事務結束，使用UPDLOCK的作用是允許用戶先讀取數據（而且不阻塞其他用戶讀數據），並且保證在後來再更新數據時，這一段時間內這些數據沒有被其他用戶修改

SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除

四、sql (server) 行鎖，表鎖案例

--設table1(A,B,C) 
A B C 
a1 b1 c1 
a2 b2 c2 
a3 b3 c3

1）排它鎖 tablockx

新建兩個連接
在第一個連接中執行以下語句

begin tran 
update table1 set A='aa' where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

在第二個連接中執行以下語句

begin tran 
select * from table1 where B='b2' 
commit tran 

若同時執行上述兩個語句，則select查詢必須等待update執行完畢才能執行即要等待30秒

2）共用鎖

在第一個連接中執行以下語句

begin tran 
select * from table1 holdlock -holdlock  人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 

在第二個連接中執行以下語句

begin tran 
select A,C from table1 where B='b2' 
update table1 set A='aa' where B='b2' 
commit tran

若同時執行上述兩個語句，則第二個連接中的select查詢可以執行
而update必須等待第一個事務釋放共用鎖轉為排它鎖後才能執行 即要等待30秒

3）死鎖

--增設table2(D,E) 
D E 
d1 e1 
d2 e2

在第一個連接中執行以下語句

begin tran 
update table1 set A='aa' where B='b2' 
waitfor delay '00:00:30' 
update table2 set D='d5' where E='e1' 
commit tran

在第二個連接中執行以下語句

begin tran 
update table2 set D='d5' where E='e1' 
waitfor delay '00:00:10' 
update table1 set A='aa' where B='b2' 
commit tran

同時執行，系統會檢測出死鎖，並中止進程

參考：

https://www.php.cn/mysql-tutorials-123100.html

https://blog.csdn.net/softuse/article/details/121940804