鎖不住的查詢

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

USE master;
GO
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
GO
-- =======================================
-- 建立測(cè)試數(shù)據(jù)庫(kù)
-- a. 刪除測(cè)試庫(kù), 如果已經(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. 建立測(cè)試數(shù)據(jù)庫(kù)
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
-- =======================================
-- 建立測(cè)試表
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

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

-- =======================================
-- 測(cè)試連接1 - 加鎖
BEGIN TRAN
--測(cè)試的初衷是通過(guò)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;

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

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

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

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

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

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

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

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

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

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

共享鎖

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

更新鎖

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

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

排他鎖

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

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

意向鎖

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

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

相關(guān)推薦：

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

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

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

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