最近在處理一個鎖的問題時，發(fā)現(xiàn)一個比較郁悶的事，使用X鎖居然無法鎖住查詢，模擬這個問題，可以使用如下T-SQL腳本來建立測試環(huán)境。

USE master;
GO
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
GO
-- =======================================
-- 建立測試數(shù)據(jù)庫
-- a. 刪除測試庫, 如果已經(jīng)存在的話
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. 建立測試數(shù)據(jù)庫
CREATE DATABASE db_xlock_test;
-- c. 關(guān)閉READ_COMMITTED_SNAPSHOT 以保持SELECT 的默認(rèn)加鎖模式
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

然后，建立一個連接，執(zhí)行下面的腳本來實現(xiàn)加鎖。

-- =======================================
-- 測試連接1 - 加鎖
BEGIN TRAN
--測試的初衷是通過SELECT加鎖，結(jié)果發(fā)現(xiàn)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;

通過執(zhí)行結(jié)果，可以看到對象被加鎖的情況：表級和頁級上是IX鎖，記錄上是X鎖。

然后新建一個連接，執(zhí)行下面的T-SQL查詢，看看會否被連接1鎖住

-- =======================================
-- 測試連接2 - 被阻塞(在測試連接1 執(zhí)行后執(zhí)行)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM dbo.tb
WHERE id <= 2;

上述查詢會很快返回結(jié)果，并不會被查詢1阻塞住。

按照我們的了解（聯(lián)機(jī)幫助上也有說明），在READ COMMITTED事務(wù)隔離級別下，查詢使用共享鎖（S），而根據(jù)鎖的兼容級別，S鎖是與X鎖沖突的，所以正常情況下，連接2的查詢需要等待連接1執(zhí)行完成。可是測試的結(jié)果去違反了這一原則。

為了了解為什么連接2不會被阻塞，對連接2做了一個Trace，發(fā)現(xiàn)一個更郁悶的問題，Trace的結(jié)果如下：

Trace的前面兩行是連接2的Trace結(jié)果，從結(jié)果看，連接2僅使用了意向共享鎖（IS），而且只是表級和頁級，按照鎖的兼容性原則，IS和IX（連接1在表級和頁級僅使用了IX鎖）是不沖突的，所以連接2的查詢不會被阻塞。在增加了查詢的數(shù)據(jù)量后，Trace結(jié)果表明查還是只在表級和頁級使用了IS鎖（Trace結(jié)果的最后4行）。

對于這個問題，解決的辦法當(dāng)然就是提升連接1鎖的粒度，使用PAGLOCK表提示將鎖的粒度提升到頁級，這樣IS與X是沖突的，就可以成功阻塞連接2。

但疑問就是，為什么查詢只在表級和頁級下意向共享鎖（IS），而不在行級下共享鎖（X），這個似乎與聯(lián)機(jī)幫助上的說明不一樣（還是一直以來理解上的偏差呢）。

附：聯(lián)機(jī)幫助上關(guān)于鎖模式的說明

共享鎖

共享鎖（S 鎖）允許并發(fā)事務(wù)在封閉式并發(fā)控制下讀取 (SELECT) 資源。

更新鎖

更新鎖（U 鎖）可以防止常見的死鎖。在可重復(fù)讀或可序列化事務(wù)中，此事務(wù)讀取數(shù)據(jù) [獲取資源（頁或行）的共享鎖（S 鎖）]，然后修改數(shù)據(jù) [此操作要求鎖轉(zhuǎn)換為排他鎖（X 鎖）]。如果兩個事務(wù)獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務(wù)嘗試將鎖轉(zhuǎn)換為排他鎖（X 鎖）。共享模式到排他鎖的轉(zhuǎn)換必須等待一段時間，因為一個事務(wù)的排他鎖與其他事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務(wù)試圖獲取排他鎖（X 鎖）以進(jìn)行更新。由于兩個事務(wù)都要轉(zhuǎn)換為排他鎖（X 鎖），并且每個事務(wù)都等待另一個事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問題，請使用更新鎖（U 鎖）。一次只有一個事務(wù)可以獲得資源的更新鎖（U 鎖）。如果事務(wù)修改資源，則更新鎖（U 鎖）轉(zhuǎn)換為排他鎖（X 鎖）。

排他鎖

排他鎖（X 鎖）可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。使用排他鎖（X 鎖）時，任何其他事務(wù)都無法修改數(shù)據(jù)；僅在使用 NOLOCK 提示或未提交讀隔離級別時才會進(jìn)行讀取操作。

數(shù)據(jù)修改語句（如 INSERT、UPDATE 和 DELETE）合并了修改和讀取操作。語句在執(zhí)行所需的修改操作之前首先執(zhí)行讀取操作以獲取數(shù)據(jù)。因此，數(shù)據(jù)修改語句通常請求共享鎖和排他鎖。例如，UPDATE 語句可能根據(jù)與一個表的聯(lián)接修改另一個表中的行。在此情況下，除了請求更新行上的排他鎖之外，UPDATE 語句還將請求在聯(lián)接表中讀取的行上的共享鎖。

意向鎖

數(shù)據(jù)庫引擎使用意向鎖來保護(hù)共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結(jié)構(gòu)的底層資源上。意向鎖之所以命名為意向鎖，是因為在較低級別鎖前可獲取它們，因此會通知意向?qū)㈡i放置在較低級別上。

本文講解了鎖不住的查詢，更多相關(guān)內(nèi)容，請關(guān)注php中文網(wǎng)。

相關(guān)推薦：

講解更新鎖（U）與排它鎖（X）的相關(guān)知識

SQL Server 2008 處理隱式數(shù)據(jù)類型轉(zhuǎn)換在執(zhí)行計劃中的增強(qiáng)

如何讓MySQL中單句實現(xiàn)無限層次父子關(guān)系查詢

以上就是鎖不住的查詢的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！