最近在處理一個鎖的問題時，發現一個比較鬱悶的事，使用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 <= 2; SELECT spid = @@SPID, tran_count = @@TRANCOUNT, database_name = DB_NAME(), object_id = OBJECT_ID(N'dbo.tb', N'Table'); -- 显示锁 EXEC sp_lock@@SPID;
#透過執行結果，可以看到物件被加鎖的情況：表格層級和頁級上是IX鎖定，記錄上是X鎖定。
#spid |
##tran_count |
#資料庫名稱 |
object_id |
|||||||
| ##51
|
1
|
#db_xlock_test |
||||||||
|
21575115 ########################spid# ## ###### |
##dbid |
#ObjId |
IndId |
##類型 |
##資源 |
|||||
|
狀態############################51#### ## ############7###################0###### |
0 |
DB |
##S |
GRANT |
||||||
| 51 |
7 |
#21575115 |
#1 |
PAG |
0.095138889 |
##IX
|
#GRANT # |
|||
51 |
7 |
21575115 |
#0 |
##TAB |
||||||
##IX |
|
###################################### ####### #################51##################1######## ####### ####1131151075##################0##################TAB ###### |
##GRANT | |||||||
#51 |
#7 |
##21575115 |
#1 |
##KEY |
#(020068e8b274) |
X |
||||
|
#授予 #####################51###### |
7 |
21575115 |
# 1 |
KEY |
#-10086470766 |
#X |
||||
| # | ## | # | ||||||||
然后新建一个连接，执行下面的T-SQL查询，看看会否被连接1锁住
-- ======================================= -- 测试连接2 - 被阻塞(在测试连接1 执行后执行) SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT * FROM dbo.tb WHERE id <= 2;
上述查询会很快返回结果，并不会被查询1阻塞住。
按照我们的了解（联机帮助上也有说明），在READ COMMITTED事务隔离级别下，查询使用共享锁（S），而根据锁的兼容级别，S锁是与X锁冲突的，所以正常情况下，连接2的查询需要等待连接1执行完成。可是测试的结果去违反了这一原则。
為了了解為什麼連線2不會被阻塞，對連線2做了一個Trace，發現一個更鬱悶的問題，Trace的結果如下：
##EventClass |
#TextData |
|||
|
####Mode###########################Lock:Acquired## #### |
21575115 |
5 - 物件 |
#6 - IS |
|
# 鎖定：已取得 |
1:77 |
0 |
6 - 頁 |
##6 - IS |
鎖定：已取得 |
##[規劃指南]
|
0
|
#2 - 資料庫 # |
3 - S |
「鎖定：已取得 |
| 21575115|||
| #6 - IS#鎖定：已取得 | 1:77 |
||
| ###6 - IS######################## 鎖定：已取得## ### #############1:80###### |
0 |
6 - 頁 |
#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中文網。
相關推薦：
#SQL Server 2008 處理隱含資料型別轉換在執行計畫中的增強功能
以上是鎖不住的查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！
