鎖不住的查詢

最近在處理一個鎖的問題時，發現一個比較鬱悶的事，使用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&#39;dbo.tb&#39;, N&#39;Table&#39;);
-- 显示锁
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的結果如下：

ObjectIDType215751155 - 物件#6 - IS06 - 頁 1:890##6 - 頁6 - IS

##EventClass	#TextData
####Mode###########################Lock:Acquired## ####		21575115	5 - 物件	#6 - IS
# 鎖定：已取得	1:77	0	6 - 頁	##6 - IS
鎖定：已取得	##[規劃指南]	0	#2 - 資料庫 #	3 - S
「鎖定：已取得
			#鎖定：已取得	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中文網。

相關推薦：

#講解更新鎖定（U）與排它鎖定（X）的相關知識

#SQL Server 2008 處理隱含資料型別轉換在執​​行計畫中的增強功能

#如何讓MySQL中單一句子實作無限層次父子關係查詢

以上是鎖不住的查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！