database如何實現鎖不住的查詢

這篇文章主要介紹了如何實現鎖不住的查詢，具有一定借鑒價值，需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。

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鎖。

然后新建一個連接，執行下面的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的結果如下：

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 鎖）放置在鎖層次結構的底層資源上。意向鎖之所以命名為意向鎖，是因為在較低級別鎖前可獲取它們，因此會通知意向將鎖放置在較低級別上。

感謝你能夠認真閱讀完這篇文章，希望小編分享如何實現鎖不住的查詢內容對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，遇到問題就找億速云，詳細的解決方法等著你來學習!