[SQLServer]広範囲のロックが発生するDB操作を安全に行う方法

広範囲のロックを取得するリスク

ALTER TABLEなど、DBテーブルスキーマに依存する変更を加える場合、スキーマ修正ロック(以下Sch-M)という強力なロックを取得する必要があります。

サービスのメンテナンス時などテーブルアクセスが少ない状態であれば問題ありませんが、DBがオンラインの状態で実施するのはブロッキングのリスクが高く、障害になりかねません。

これについての対策方法をご紹介します。

SQLServerのブロッキングチェーンの仕組み

ロックは待機リストによって管理されます。
複数のブロッキングが連なるとブロッキングチェーンが発生します。
SQLServerのロックの仕組みとして、あるクエリが実行された際そのクエリより前のクエリのうち競合するものはすべてブロッキング要因となります。

参考：SQL Server / SQL Database パフォーマンスチューニング & トラブルシューティング シリーズ : SQL Server のロックの基本的な動作

Sch-Mは、通常のSELECT文による共有ロック(S)をはじめ、with(nolock)オプションをつけたSELECT文が取得するスキーマ共有ロック(Sch-S)とも競合しあらゆるクエリと競合します。
つまりSch-Mが待機リストに存在する場合、そのテーブルに対する後続のクエリはすべてブロッキングされます。

参考：ロックの互換性

N:競合しない
C:競合する

特にリスクの高いレプリケーション設定作業

SQLServerのレプリケーション追加時は、初回のスナップショット取得時にSch-Mを取ります。
追加対象のテーブルだけでなく、同じパブリケーションでレプリケーションしている他テーブルにも順番にSch-Mを瞬間的に取得する必要があり、それらのテーブルに対して実行されている他のクエリと競合します。

スナップショット処理は途中で止めることにもリスクがあるため、実行したら基本的にキャンセルできません。
スナップショット処理によってブロッキングが発生した場合はルートブロッカーとなっているクエリをkillすることでスナップショット処理が進むようにするしかなく、即時の対応が求められます。

これを回避するため、レプリケーション追加作業はアクセスの少ない時間帯を見計らう必要があり対策をを考えました。

対策

レプリケーション作業に限らず、ブロッキングのリスクがある作業を行う場合、作業直前にそれと同等のロックが取れるかどうかを確認することで、安全に作業できるようになるでしょう。

確認クエリにはSET LOCK_TIMEOUT 200のように設定し、200msでロックを取得できない場合はクエリ強制終了することで安全に確認できます。

• 確認クエリが成功する場合... 今は作業をしてもブロッキングが発生しないと判断
• 確認クエリがタイムアウトする場合... 今作業をするとブロッキングが発生すると判断

以下のようにロック種別を指定してSELECT文を実行することで任意のロックを取ることができないかと考えましたが、Sch-Mはこの方法で指定することはできませんでした。

SELECT TOP 1 * FROM [対象テーブル] WITH([ロックの種類], TABLOCK)

Sch-Mは排他ロック(X)と競合範囲が似ていますが、違いはSch-Sと競合するかどうかのみです。

代替手段として、以下の2つの確認を組み合わせることで、Sch-M相当のロック取得できるかを確認することができそうです。

1. 排他ロック(X)を取得できるか確認
2. スキーマ共有ロック(Sch-S)を取得しているクエリが実際に実行されているか確認

以下の例はレプリケーション追加により発生するSch-Mロックを取得できるかを確認するクエリです。
クエリの中で上記の1,2を両方確認しています。

USE [****] -- ★対象DBを指定
GO
     
DECLARE @publisherName sysname = N'********' -- ★対象パブリケーションを指定

-- 安全のため200msでタイムアウトするように設定
SET LOCK_TIMEOUT 200;
SET NOCOUNT ON;
SET XACT_ABORT ON;
     
     
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
  
SELECT *
INTO #TargetTable
FROM (
    SELECT name FROM dbo.sysarticles where pubid IN (
        SELECT pubid FROM dbo.syspublications WHERE name = @publisherName
    )
    UNION
    SELECT name FROM sys.tables WHERE name IN ('********')  -- ★対象テーブルを指定
 ) AS target (name)
   
      
DECLARE @articleName sysname
      
DECLARE curArticleList CURSOR LOCAL FAST_FORWARD
for
SELECT name FROM dbo.sysarticles where pubid IN (
    SELECT pubid FROM dbo.syspublications WHERE name = @publisherName
)
UNION
SELECT name FROM sys.tables WHERE name IN ('*******')  -- ★対象テーブルを指定
  
  
-- 1.対象テーブルに対して排他ロック(X)を取得できるか確認
OPEN curArticleList
     
FETCH NEXT FROM curArticleList INTO @articleName
     
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql nvarchar(max)
    SET @sql = 'SELECT TOP 1 ''' + QUOTENAME(@articleName) + ''' AS ArticleName, 1 AS Result FROM ' + QUOTENAME(@articleName) + ' WITH(XLOCK, TABLOCK);'
     
    BEGIN TRY
        EXECUTE (@sql)
    END TRY
    BEGIN CATCH
        SET @sql = 'SELECT ''' + QUOTENAME(@articleName) + ''' AS ArticleName, ''ERROR : ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + ' : '  + ERROR_MESSAGE() + ''' AS Result'
        EXECUTE(@sql)
        SELECT
            tl.request_session_id, es.status,OBJECT_NAME(tl.resource_associated_entity_id, resource_database_id) AS object_name,
            tl.resource_type, tl.request_type, tl.request_status, es.program_name, es.host_name,
            es.last_request_start_time, es.last_request_end_time, DATEDIFF(ss, es.last_request_end_time, GETDATE()) as elasped_time_since_last_run
        FROM
            sys.dm_tran_locks AS tl
            INNER JOIN sys.dm_exec_sessions AS es
                on es.session_id = tl.request_session_id
        WHERE
            tl.resource_type = 'OBJECT' AND tl.resource_associated_entity_id =  OBJECT_ID(@articleName)
    END CATCH
    FETCH NEXT FROM curArticleList INTO @articleName
END
      
CLOSE curArticleList
DEALLOCATE curArticleList

-- 2.対象テーブルに対してSch-Sを取得しているクエリが実行されているか確認。何も表示されなければOK
 SELECT
    es.session_id,
    es.status AS session_status,
    es.host_name,
    es.program_name,
    es.login_name,
    o.name AS object_name,
    l.request_mode,
    r.request_id,
    r.start_time,
    r.status AS request_status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text AS sql_text
FROM
    sys.dm_tran_locks l WITH (NOLOCK)
JOIN
    sys.objects o WITH (NOLOCK) ON l.resource_associated_entity_id = o.object_id
LEFT JOIN
    sys.dm_exec_requests r WITH (NOLOCK) ON l.request_session_id = r.session_id
LEFT JOIN
    sys.dm_exec_sessions es WITH (NOLOCK) ON l.request_session_id = es.session_id
OUTER APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
INNER JOIN #TargetTable as target ON o.name = target.name
WHERE
    l.resource_type = 'OBJECT'
    AND l.request_mode = 'Sch-S'  -- Sch-Sロック
   
IF OBJECT_ID('tempdb..#TargetTable') IS NOT NULL
    DROP TABLE #TargetTable;

WHILE(@@TRANCOUNT > 0)
BEGIN
    ROLLBACK TRAN
END
GO

このクエリではロックを取れなかった場合ブロッキングの詳細を出力するようにしています。

BEGIN CATCH
        SET @sql = 'SELECT ''' + QUOTENAME(@articleName) + ''' AS ArticleName, ''ERROR : ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + ' : '  + ERROR_MESSAGE() + ''' AS Result'
        EXECUTE(@sql)
        SELECT
            tl.request_session_id, es.status,OBJECT_NAME(tl.resource_associated_entity_id, resource_database_id) AS object_name,
            tl.resource_type, tl.request_type, tl.request_status, es.program_name, es.host_name,
            es.last_request_start_time, es.last_request_end_time, DATEDIFF(ss, es.last_request_end_time, GETDATE()) as elasped_time_since_last_run
        FROM
            sys.dm_tran_locks AS tl
            INNER JOIN sys.dm_exec_sessions AS es
                on es.session_id = tl.request_session_id
        WHERE
            tl.resource_type = 'OBJECT' AND tl.resource_associated_entity_id =  OBJECT_ID(@articleName)
    END CATCH

このクエリを作業直前に何度か実行してみます。

1. 排他ロック(X)を取得できるか確認
2. スキーマ共有ロック(Sch-S)を取得しているクエリが実際に実行されているか確認

1が問題ないことを確認しつつ、2の結果が毎回異なるようであれば、瞬間的なSch-Sしか取られていないことになるので、Sch-Mの取得により長時間ブロッキングが発生することはないでしょう。
仮に何度実行しても特定のスロークエリがSch-Sを取得し続けている場合、それがルートブロッカーになりうるのでブロッキングが発生するでしょう。

まとめ

広範囲のロックを取得する作業の直前に確認クエリを実行することで、Sch-Sも含めてブロッキングの事前確認ができるようになりました。
実際に何度かレプリケーション作業で利用しましたが、実際にブロッキングを回避することができています。