Sql-Server

在 Sql Server 中，有沒有辦法檢查一組選定的行是否被鎖定？

  • March 28, 2019

我們正在嘗試更新/刪除數十億行表中的大量記錄。由於這是一張流行的表格，因此該表格的不同部分有很多活動。任何大型更新/刪除活動都會被長時間阻塞（因為它正在等待獲得所有行的鎖或頁面鎖或表鎖），從而導致超時或需要多天才能完成任務。

因此，我們正在改變一次刪除小批量行的方法。但是我們想檢查選定的（比如說 100 或 1000 或 2000 行）目前是否被不同的程序鎖定。

  • 如果沒有，則繼續刪除/更新。
  • 如果它們被鎖定，則轉到下一組記錄。
  • 最後，回到開頭並嘗試更新/刪除遺漏的內容。

這是可行的嗎？

謝謝， ToC

如果我正確理解請求，目標是刪除成批的行，同時 DML 操作正在整個表的行上發生。目標是刪除一批；但是，如果包含在該批次定義的範圍內的任何底層行被鎖定，那麼我們必須跳過該批次並移動到下一個批次。然後我們必須返回到之前未刪除的任何批次，並重試我們原來的刪除邏輯。我們必須重複這個循環，直到所有需要的行批次都被刪除。

如前所述，使用 READPAST 提示和 READ COMMITTED（預設）隔離級別是合理的，以便跳過可能包含阻塞行的過去範圍。我會更進一步，建議使用 SERIALIZABLE 隔離級別和蠶食刪除。

SQL Server 使用 Key-Range 鎖來保護隱式包含在 Transact-SQL 語句讀取的記錄集中的一系列行，同時使用可序列化事務隔離級別…在此處了解更多資訊： https ://technet.microsoft.com /en-US/library/ms191272(v=SQL.105).aspx

使用蠶食刪除，我們的目標是隔離一系列行，並確保在刪除它們時不會對這些行發生任何更改，也就是說，我們不希望幻讀或插入。可序列化的隔離級別就是為了解決這個問題。

在展示我的解決方案之前，我想補充一點，我既不建議將數據庫的預設隔離級別切換為 SERIALIZABLE，也不建議我的解決方案是最好的。我只是想介紹它，看看我們可以從這裡去哪裡。

一些家政注意事項：

  1. 我使用的 SQL Server 版本是 Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
  2. 我的測試數據庫正在使用 FULL 恢復模型

為了開始我的實驗，我將建立一個測試數據庫、一個範例表，並在表中填充 2,000,000 行。


USE [master];
GO

SET NOCOUNT ON;

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
   ALTER DATABASE [test] SET SINGLE_USER
       WITH ROLLBACK IMMEDIATE;
   DROP DATABASE [test];
END
GO

-- Create the test database
CREATE DATABASE [test];
GO

-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;

-- Create a FULL database backup
-- in order to ensure we are in fact using 
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO

USE [test];
GO

-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
   DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
     c1 BIGINT IDENTITY (1,1) NOT NULL
   , c2 INT NOT NULL
) ON [PRIMARY];
GO

-- Insert 2,000,000 rows 
INSERT INTO dbo.tbl
   SELECT TOP 2000
       number
   FROM
       master..spt_values
   ORDER BY 
       number
GO 1000

此時，我們將需要一個或多個索引，SE​​RIALIZABLE 隔離級別的鎖定機制可以作用於這些索引。


-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
   ON dbo.tbl (c1);
GO

-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2 
   ON dbo.tbl (c2);
GO

現在，讓我們檢查一下是否創建了 2,000,000 行


SELECT
   COUNT(*)
FROM
   tbl;

在此處輸入圖像描述

所以，我們有我們的數據庫、表、索引和行。因此，讓我們設置蠶食刪除的實驗。首先，我們必須決定如何最好地創建一個典型的蠶食刪除機制。


DECLARE
     @BatchSize        INT    = 100
   , @LowestValue      BIGINT = 20000
   , @HighestValue     BIGINT = 20010
   , @DeletedRowsCount BIGINT = 0
   , @RowCount         BIGINT = 1;

SET NOCOUNT ON;
GO

WHILE  @DeletedRowsCount &lt  ( @HighestValue - @LowestValue ) 
BEGIN

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION

       DELETE 
       FROM
           dbo.tbl 
       WHERE
           c1 IN ( 
                   SELECT TOP (@BatchSize)
                       c1
                   FROM
                       dbo.tbl 
                   WHERE 
                       c1 BETWEEN @LowestValue AND @HighestValue
                   ORDER BY 
                       c1
                 );

       SET @RowCount = ROWCOUNT_BIG();

   COMMIT TRANSACTION;

   SET @DeletedRowsCount += @RowCount;
   WAITFOR DELAY '000:00:00.025';
   CHECKPOINT;

END;

如您所見，我將顯式事務放置在 while 循環中。如果您想限制日誌刷新，請隨意將其放在循環之外。此外，由於我們處於 FULL 恢復模式，您可能希望在執行蠶食刪除操作時更頻繁地創建事務日誌備份，以確保可以防止您的事務日誌異常增長。

所以，我對這個設置有幾個目標。首先，我想要我的鑰匙範圍鎖；所以，我盡量保持批次盡可能小。我也不想對我的“巨大”表的並發性產生負面影響；所以，我想盡快把我的鎖拿走。所以，我建議你把你的批量縮小。

現在，我想提供一個非常簡短的範例來說明這個刪除常式的實際應用。我們必須在 SSMS 中打開一個新視窗並從表中刪除一行。我將使用預設的 READ COMMITTED 隔離級別在隱式事務中執行此操作。


DELETE FROM
   dbo.tbl
WHERE
   c1 = 20005;

這一行真的被刪除了嗎？


SELECT
   c1
FROM
   dbo.tbl
WHERE
   c1 BETWEEN 20000 AND 20010;

是的，它被刪除了。

刪除行的證明

現在，為了查看我們的鎖，讓我們在 SSMS 中打開一個新視窗並添加一兩個程式碼片段。我正在使用 Adam Mechanic 的 sp_whoisactive，可以在這裡找到：sp_whoisactive


SELECT
   DB_NAME(resource_database_id) AS DatabaseName
 , resource_type
 , request_mode
FROM
   sys.dm_tran_locks
WHERE
   DB_NAME(resource_database_id) = 'test'
   AND resource_type = 'KEY'
ORDER BY
   request_mode;

-- Our insert
sp_lock 55;

-- Our deletions
sp_lock 52;

-- Our active sessions
sp_whoisactive;

現在，我們準備開始了。在一個新的 SSMS 視窗中，讓我們開始一個顯式事務，該事務將嘗試重新插入我們刪除的一行。同時，我們將啟動我們的蠶食刪除操作。

插入程式碼：


BEGIN TRANSACTION

   SET IDENTITY_INSERT dbo.tbl ON;

   INSERT  INTO dbo.tbl
           ( c1 , c2 )
   VALUES
           ( 20005 , 1 );

   SET IDENTITY_INSERT dbo.tbl OFF;

--COMMIT TRANSACTION;

讓我們開始這兩個操作，從插入開始，然後是刪除。我們可以看到鍵範圍鎖和排他鎖。

範圍鎖和排他鎖

插入生成了這些鎖：

插入鎖

蠶食刪除/選擇持有這些鎖：

在此處輸入圖像描述

我們的插入按預期阻止了我們的刪除：

插入塊刪除

現在，讓我們送出插入事務，看看發生了什麼。

送出刪除

正如預期的那樣，所有交易都完成了。現在，我們必須檢查插入是否是幻像，或者刪除操作是否也將其刪除。


SELECT
   c1
FROM
   dbo.tbl
WHERE
   c1 BETWEEN 20000 AND 20015;

實際上，插入已被刪除；因此，不允許幻像插入。

無幻影外掛

所以，總而言之，我認為這個練習的真正目的不是嘗試跟踪每一個行、頁或表級別的鎖，並嘗試確定批處理的元素是否被鎖定，因此需要我們的刪除操作來等待。這可能是提問者的意圖；然而，這項任務是艱鉅的，即使不是不可能，也基本上是不切實際的。真正的目標是確保一旦我們用自己的鎖隔離了批次的範圍，然後再刪除批次，就不會出現不需要的現象。SERIALIZABLE 隔離級別實現了這個目標。關鍵是要保持小塊，控制交易日誌，並消除不需要的現象。

如果您想要速度，那麼不要建構無法分區的超深表，因此無法使用分區切換來獲得最快的結果。速度的關鍵是分區和並行；痛苦的關鍵是啃咬和活鎖。

請讓我知道你在想什麼。

我創建了一些實際的 SERIALIZABLE 隔離級別範例。它們應該在下面的連結中可用。

刪除操作

插入操作

等式運算 - 下一個鍵值上的鍵範圍鎖定

平等操作 - 現有數據的單例獲取

平等運算 - 不存在數據的單例提取

不等式運算 - 範圍和下一個鍵值上的鍵範圍鎖定

因此，我們正在改變一次刪除小批量行的方法。

這是一個非常好的小批量或小塊刪除的好主意。我會根據數據庫的恢復模型添加一個小的和 - if , then do a and if then do a以避免事務日誌膨脹 - 在批次之間。waitfor delay '00:00:05'``FULL``log backup``SIMPLE``manual CHECKPOINT

但是我們想檢查選定的（比如說 100 或 1000 或 2000 行）目前是否被不同的程序鎖定。

您所說的內容並非完全可能開箱即用（請記住您的 3 個要點）。如果上述建議 -small batches + waitfor delay不起作用（前提是您進行了適當的測試），那麼您可以使用query HINT.

不要使用NOLOCK- 請參閱kb/308886、 Itzik Ben-Gan 的 SQL Server 讀取一致性問題、 Aaron Bertrand 的無處不在的 NOLOCK和SQL Server NOLOCK 提示以及其他糟糕的想法。

READPAST提示將有助於您的方案。提示的要點READPAST是 - 如果存在行級鎖，則 SQL 伺服器不會讀取它。

指定數據庫引擎不讀取被其他事務鎖定的行。指定時READPAST，將跳過行級鎖。也就是說，數據庫引擎會跳過行而不是阻塞目前事務，直到釋放鎖為止。

DELETE from schema.tableName with (READPAST, READCOMMITTEDLOCK)在我有限的測試中，我發現在使用查詢會話隔離級別READ COMMITTED並將SET TRANSACTION ISOLATION LEVEL READ COMMITTED其設置為預設隔離級別時，吞吐量非常好。

引用自：https://dba.stackexchange.com/questions/102160

相關問答

Sql-Server

不返回記錄的查詢掛在鎖上

  • February 4, 2022
檢視問答
Sql-Server

為什麼 UPDLOCK 會導致 SELECT 掛起（鎖定）？

  • February 4, 2022
檢視問答
Sql-Server

阻塞寫入器時 SELECT 語句的 TABLOCK 提示不是問題

  • June 22, 2021
檢視問答
Sql-Server

DBA 阻止查詢電子郵件警報

  • March 2, 2021
檢視問答
Sql-Server

我們可以減少索引上的行鎖爭用嗎

  • July 1, 2020
檢視問答
Sql-Server

阻塞程序報告 - lockmode 適用於哪個程序？

  • December 30, 2019
檢視問答