一. 為什么要引入鎖
多個用戶同時對資料庫的并發操作時會帶來以下資料不一致的問題，
二、鎖的分類
（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 資料庫引擎具有多粒度鎖定，允許一個事務鎖定不同型別的資源， 為了盡量減少鎖定的開銷，資料庫引擎自動將資源鎖定在適合任務的級別， 鎖定在較小的粒度（例如行）可以提高并發度，但開銷較高，因為如果鎖定了許多行，則需要持有更多的鎖， 鎖定在較大的粒度（例如表）會降低了并發度，因為鎖定整個表限制了其他事務對表中任意部分的訪問， 但其開銷較低，因為需要維護的鎖較少，
| 資源 | 說明 |
|---|---|
| RID | 用于鎖定堆中的單個行的行識別符號， |
| KEY | 索引中用于保護可序列化事務中的鍵范圍的行鎖， |
| PAGE | 資料庫中的8KB頁，例如資料頁或索引頁， |
| EXTENT | 一組連續的八頁，例如資料頁或索引頁， |
| HoBT | 堆或B樹，用于保護沒有聚集索引的表中的B樹（索引）或堆資料頁的鎖， |
| TABLE | 包括所有資料和索引的整個表， |
| FILE | 資料庫檔案， |
| APPLICATION | 應用程式專用的資源， |
| METADATA | 元資料鎖， |
| ALLOCATION_UNIT | 分配單元， |
| DATABASE | 整個資料庫， |
三、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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499171.html
標籤：SQL Server