最近在處理一個鎖的問題時，發現一個比較郁悶的事，使用X鎖居然無法鎖住查詢，模擬這個問題，可以使用如下T-sql腳本來建立測試環境。
USE?master; GO IF?@@TRANCOUNT?>?0 ROLLBACK?TRAN; GO --?======================================= --?建立測試數據庫 --?a.?刪除測試庫,?如果已經存在的話 IF?DB_ID(N'db_xlock_test')?IS?NOT?NULL BEGIN; ALTER?DATABASE?db_xlock_test SET?SINGLE_USER WITH ROLLBACK?AFTER?0; DROP?DATABASE?db_xlock_test; END; --?b.?建立測試數據庫 CREATE?DATABASE?db_xlock_test; --?c.?關閉READ_COMMITTED_SNAPSHOT?以保持select?的默認加鎖模式 ALTER?DATABASE?db_xlock_test SET?READ_COMMITTED_SNAPSHOT?OFF; GO --?======================================= --?建立測試表 USE?db_xlock_test; GO CREATE?TABLE?dbo.tb( id?int?IDENTITY PRIMARY?KEY, name?sysname ); INSERT?dbo.tb SELECT?TOP(50000) O1.name?+?N'.'?+?O2.name?+?N'.'?+?O3.name FROM?sys.objects?O1?WITH(NOLOCK), sys.objects?O2?WITH(NOLOCK), sys.objects?O3?WITH(NOLOCK); GO
然后，建立一個連接，執行下面的腳本來實現加鎖。
--?======================================= --?測試連接1?-?加鎖 BEGIN?TRAN --測試的初衷是通過SELECT加鎖，結果發現UPDATE也鎖不住 UPDATE?dbo.tb?SET?name?=?name --SELECT?COUNT(*)?FROM?dbo.tb?WITH(XLOCK) WHERE?id?<p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">通過執行結果，可以看到對象被加鎖的情況：表級和頁級上是</span><span style="font-family: Calibri;">IX</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">鎖，記錄上是</span><span style="font-family: Calibri;">X</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">鎖。</span></span></p>
|
spid |
tran_count |
database_name |
object_id |
|||||||
|
51 |
1 |
db_xlock_test |
21575115 |
|||||||
|
spid |
dbid |
ObjId |
IndId |
Type |
Resource |
Mode |
Status |
|||
|
51 |
7 |
0 |
0 |
DB |
S |
GRANT |
||||
|
51 |
7 |
21575115 |
1 |
PAG |
0.095138889 |
IX |
GRANT |
|||
|
51 |
7 |
21575115 |
0 |
TAB |
IX |
GRANT |
||||
|
51 |
1 |
1131151075 |
0 |
TAB |
IS |
GRANT |
||||
|
51 |
7 |
21575115 |
1 |
KEY |
(020068e8b274) |
X |
GRANT |
|||
|
51 |
7 |
21575115 |
1 |
KEY |
-10086470766 |
X |
GRANT |
|||
| ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? |
然后新建一個連接，執行下面的T-SQL查詢，看看會否被連接1鎖住
--?======================================= --?測試連接2?-?被阻塞(在測試連接1?執行后執行) SET?TRANSACTION?ISOLATION?LEVEL?READ?COMMITTED; SELECT?*?FROM?dbo.tb WHERE?id?<p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">上述查詢會很快返回結果，并不會被查詢</span><span style="font-family: Calibri;">1</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">阻塞住。</span></span></p><p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">按照我們的了解（聯機幫助上也有說明），在</span><span style="color: red;"><span style="font-family: Calibri;">READ COMMITTED</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">事務隔離級別下，查詢使用共享鎖（</span><span style="color: red;"><span style="font-family: Calibri;">S</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">），而根據鎖的兼容級別，</span><span style="color: red;"><span style="font-family: Calibri;">S</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">鎖是與</span><span style="color: red;"><span style="font-family: Calibri;">X</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">鎖沖突的，所以正常情況下，連接</span><span style="color: red;"><span style="font-family: Calibri;">2</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">的查詢需要等待連接</span><span style="color: red;"><span style="font-family: Calibri;">1</span></span><span style="font-family: 宋體; color: red; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">執行完成。可是測試的結果去違反了這一原則。</span></span></p><p style="margin: 0cm 0cm 0pt; text-align: left;"><span style="font-size: small;"><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">為了了解為什么連接</span><span style="font-family: Calibri;">2</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">不會被阻塞，對連接</span><span style="font-family: Calibri;">2</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">做了一個</span><span style="font-family: Calibri;">Trace</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">，發現一個更郁悶的問題，</span><span style="font-family: Calibri;">Trace</span><span style="font-family: 宋體; mso-ascii-font-family: Calibri; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: 宋體; mso-fareast-theme-font: minor-fareast; mso-hansi-font-family: Calibri; mso-hansi-theme-font: minor-latin;">的結果如下：</span></span></p>
|
EventClass |
TextData |
ObjectID |
Type |
Mode |
|
Lock:Acquired |
? |
21575115 |
5 – OBJECT |
6 – IS |
|
Lock:Acquired |
1:77 |
0 |
6 – PAGE |
6 – IS |
|
Lock:Acquired |
[PLANGUIDE] |
0 |
2 – DATABASE |
3 – S |
|
Lock:Acquired |
? |
21575115 |
5 – OBJECT |
6 – IS |
|
Lock:Acquired |
1:77 |
0 |
6 – PAGE |
6 – IS |
|
Lock:Acquired |
1:80 |
0 |
6 – PAGE |
6 – IS |
|
Lock:Acquired |
1:89 |
0 |
6 – PAGE |
6 – IS |
Trace的前面兩行是連接2的Trace結果，從結果看，連接2僅使用了意向共享鎖（IS），而且只是表級和頁級，按照鎖的兼容性原則，IS和IX（連接1在表級和頁級僅使用了IX鎖）是不沖突的，所以連接2的查詢不會被阻塞。在增加了查詢的數據量后，Trace結果表明查還是只在表級和頁級使用了IS鎖（Trace結果的最后4行）。
對于這個問題，解決的辦法當然就是提升連接1鎖的粒度，使用PAGLOCK表提示將鎖的粒度提升到頁級，這樣IS與X是沖突的，就可以成功阻塞連接2。
但疑問就是，為什么查詢只在表級和頁級下意向共享鎖（IS），而不在行級下共享鎖（X），這個似乎與聯機幫助上的說明不一樣（還是一直以來理解上的偏差呢）。
附：聯機幫助上關于鎖模式的說明
共享鎖
共享鎖（S 鎖）允許并發事務在封閉式并發控制下讀取 (SELECT) 資源。
更新鎖
更新鎖（U 鎖）可以防止常見的死鎖。在可重復讀或可序列化事務中，此事務讀取數據 [獲取資源（頁或行）的共享鎖（S 鎖）]，然后修改數據 [此操作要求鎖轉換為排他鎖（X 鎖）]。如果兩個事務獲得了資源上的共享模式鎖，然后試圖同時更新數據，則一個事務嘗試將鎖轉換為排他鎖（X 鎖）。共享模式到排他鎖的轉換必須等待一段時間，因為一個事務的排他鎖與其他事務的共享模式鎖不兼容；發生鎖等待。第二個事務試圖獲取排他鎖（X 鎖）以進行更新。由于兩個事務都要轉換為排他鎖（X 鎖），并且每個事務都等待另一個事務釋放共享模式鎖，因此發生死鎖。
若要避免這種潛在的死鎖問題，請使用更新鎖（U 鎖）。一次只有一個事務可以獲得資源的更新鎖（U 鎖）。如果事務修改資源，則更新鎖（U 鎖）轉換為排他鎖（X 鎖）。
排他鎖
排他鎖（X 鎖）可以防止并發事務對資源進行訪問。使用排他鎖（X 鎖）時，任何其他事務都無法修改數據；僅在使用 NOLOCK 提示或未提交讀隔離級別時才會進行讀取操作。
數據修改語句（如 INSERT、UPDATE 和 delete）合并了修改和讀取操作。語句在執行所需的修改操作之前首先執行讀取操作以獲取數據。因此，數據修改語句通常請求共享鎖和排他鎖。例如，UPDATE 語句可能根據與一個表的聯接修改另一個表中的行。在此情況下，除了請求更新行上的排他鎖之外，UPDATE 語句還將請求在聯接表中讀取的行上的共享鎖。
意向鎖
數據庫引擎使用意向鎖來保護共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結構的底層資源上。意向鎖之所以命名為意向鎖，是因為在較低級別鎖前可獲取它們，因此會通知意向將鎖放置在較低級別上。
本文講解了鎖不住的查詢，更多相關內容，請關注php中文網。
相關推薦：
