SQL Server: ブロッキングが発生しやすい環境でALTER系クエリを実行させるための方法について考えてみた

背景

変更の追跡をプロダクション環境へリリースする際に問題が生じました。
リリース自体は以下のクエリですぐに完了します。

--①DB上で変更の追跡の有効化
ALTER DATABASE [dbname] SET CHANGE_TRACKING = ON

--②テーブルに対して変更の追跡を有効化
ALTER TABLE [tablename] ENABLE CHANGE_TRACKING

①については、環境によっては10秒以上かかることもありましたが、②については、環境やテーブルのサイズに関わらず一瞬で完了していました。
ただし、②はテーブルのSch-Mロックを獲得する必要があるため、同じテーブルに対して実行中のクエリがあるとブロッキングでリリースができません。

また、実行しっぱなしでブロッキングで待たされている状況が解消されるのを待てばOK、というわけでもありません。
何故かというと、Sch-Mロックの獲得待ちでブロッキングされている場合、テーブルとしてはSch-Mロックが獲得されているような挙動をとります。
つまり、以下のクエリでもブロッキングされてしまいます。

select * from tablename with(nolock)

プロダクション環境ではいろいろなクエリが実行されているため、リリースのタイミングが難しいです。
これは変更の追跡に限った話ではなく、「Sch-Mロックをはじめ、ブロッキングを起こす可能性があるクエリ」を実行する際に直面する問題となります。長時間実行が必要なクエリについてはメンテを設ける等必要な可能性もありますが、ロックがとれれば一瞬で完了するクエリについてはタイミングの問題になってくるので、どうやれば他のクエリの邪魔をすることなくクエリを実行完了させられるかについて考えてみました。

方法

アイデアとしては、「ブロックされていたらいったん諦めて、再度実行する」というものです。

シンプルな実装としては以下のようになります。

SET LOCK_TIMEOUT 200
ALTER TABLE table_name ENABLE CHANGE_TRACKING
go

このクエリは、「SET LOCK_TIMEOUT」オプションを指定しているので、200ミリ秒ブロックされたら自然とタイムアウトします。
ブロッキングが発生すると、200ミリ秒は他のクエリもブロックしてしまいますが、わずかな時間なので問題とみなされることは少ないはずです。

このクエリを、成功するまで自動で何度もくり返すようにしたクエリが以下になります。

SET LOCK_TIMEOUT 200
DECLARE @tblName varchar(255) = 'table_name'
DECLARE @msg varchar(4000)

WHILE(0=0)
BEGIN
    BEGIN TRY
    EXEC ('ALTER TABLE ' + @tblname + ' ENABLE CHANGE_TRACKING')
    SET @msg =  FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss')  + ' : ' + @tblName + ' : Success'
    RAISERROR(@msg,0,0) WITH NOWAIT
    BREAK
    END TRY
    BEGIN CATCH
        SET @msg = FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss')  + ' : ' + @tblName + ' : ' + CAST(ERROR_NUMBER() as varchar(100))+ ' : ' + ERROR_MESSAGE()
        RAISERROR(@msg,0,0) WITH NOWAIT
        -- ロックタイムアウトした場合以外 (1222) は通常のエラーなのでループを抜ける
        IF ERROR_NUMBER() <> 1222
        BEGIN
            BREAK
        END
        WAITFOR DELAY '00:00:01'
    END CATCH
END

実際にプロダクション環境で実行した結果です。

タイムアウトしては1秒間待ち再実行を繰り返すことで、最終的にはクエリを実行完了させることができました。
30分以上かかって成功したケースもあり、「機械的に奇跡のタイミングを探す」というアプローチの有効性を感じました。

タイミングの調査

紹介したクエリでだいたいのケースではうまくいくと思うのですが、長時間実行が完了しない場合は、「他のクエリが実行されていない時間帯を探す」という調査と組み合わせると、より成功確率があがります。

タイミングの調査としては、「現在、そのテーブルまたはテーブルを参照しているVIEWを含むクエリが実行されているか」を例えば1分に1回ジョブで定期的に調査する、といった方法が考えられます。

自分は以下の方法で調査を行いました。

1. 実行履歴保存用テーブルを作成

--実行履歴保存用テーブル
CREATE TABLE [dbo].[UsingTableCheck](
    [table_name] [varchar](255) NULL,
    [collect_date] [datetime] NOT NULL,
    [session_id] [smallint] NOT NULL,
    [host_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [status] [nvarchar](30) NOT NULL,
    [blocking_session_id] [smallint] NULL,
    [start_time] [datetime] NOT NULL,
    [wait_type] [nvarchar](60) NULL,
    [wait_time] [int] NOT NULL,
    [last_wait_type] [nvarchar](60) NOT NULL,
    [wait_resource] [nvarchar](256) NOT NULL,
    [command] [nvarchar](32) NOT NULL,
    [text] [nvarchar](max) NULL,
    [elapsed_time_sec] [int] NULL
) 

2.指定したテーブルと、そのテーブルを参照しているVIEWのいずれかを実行中クエリに含むクエリを検知＋登録

--指定したテーブルと、そのテーブルを参照しているVIEWのいずれかを実行中クエリに含むクエリを検知し、専用のテーブルにINSERTする
--調査用ストアド
--DB単位。該当DBをuseした状態でつかう
ALTER  proc [dbo].[RegistUsingTable]
    @table_name varchar(255)
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @parameter nvarchar(max)

;WITH objectList AS(
-- 該当オブジェクトを参照しているテーブル
select
    1 as level,
    d.referenced_id AS parent_objecT_id,
    d.referenced_entity_name AS parent_object_name,
    d.referencing_id AS child_object_id,
    object_name(d.referencing_id) AS child_object_name,
    o.type
from
    sys.sql_expression_dependencies AS d WITH(NOLOCK)
    INNER JOIN sys.objects as o WITH(NOLOCK)
    ON  d.referencing_id = o.object_id
    AND o.type IN('V')
where
    d.referenced_id = OBJECT_ID(@table_name)
union all
select
    level + 1,
    c.referenced_id,
    c.referenced_entity_name,
    c.referencing_id,
    object_name(c.referencing_id) AS object_name,
    o.type
from
    sys.sql_expression_dependencies as c WITH(NOLOCK)
    INNER JOIN sys.objects as o WITH(NOLOCK)
    ON  c.referencing_id = o.object_id
    AND o.type IN('V')
    inner join objectList
    on objectList.child_object_id = c.referenced_id
)

SELECT
    @parameter = ' AND (' + 'text LIKE ''%' + @table_name + '[^0-9|a-Z|_-]%'' OR ' + STUFF(name,1,4,'') + ')'
FROM
(
    SELECT
        COALESCE(' OR text LIKE ''%' + child_object_name + '[^0-9|a-Z|_-]%''','')
    FROM
        objectList
    ORDER BY level asc
    FOR XML PATH('')
) AS T(name)
OPTION(maxdop 1)


DECLARE @sql nvarchar(max) = '
SELECT ' +
    '''' + @table_name + '''' + ',
    getdate() as collect_date,
    er.session_id,
    es.host_name,
    es.program_name,
    er.status,
    er.blocking_session_id,
    er.start_time,
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.wait_resource,
    er.command,
    st.text,
    datediff(s, er.start_time, GETDATE()) as elapsed_time_sec
FROM
    sys.dm_exec_requests  AS er
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS st
    LEFT JOIN sys.dm_exec_sessions AS es
        ON er.session_id = es.session_id
WHERE
    st.text is not NULL
    AND
    er.session_id <> @@SPID
    and datediff(s, er.start_time, GETDATE()) >= 1
'
IF @parameter IS NULL
BEGIN
    SET @parameter = ' AND (' + 'text LIKE ''%' + @table_name + '[^0-9|a-Z|_-]%'')'
END

INSERT INTO UsingTableCheck
EXECUTE (@sql + @parameter)

3. ストアドをジョブで定期実行させる

--定期実行しておくクエリ
execute [dbo].[RegistUsingTable] @table_name = 'table_name'

4. 実行履歴テーブルから、リリースのタイミングを決める

collect_date を見ながら、実行履歴テーブルにレコードが存在しないタイミングを見つけて、そのタイミングでリリースすると成功確率が高まります。

まとめ

「Sch-Mロックをはじめ、ブロッキングを起こす可能性があるクエリ」を実行する際に直面する問題を紹介し、その解決案について説明しました。同じような状況に遭遇した場合は試してみてください。

2020/07/07 追記

カーソルがOPENした状態でALTERを実行するとカーソル使っているクエリがエラーで終了してしまうので、カーソルOPENのチェックも組み込んだクエリを作成しました。

set transaction isolation level read uncommitted
set nocount on

declare @tblname varchar(255) = 'table_name'
declare @parameter nvarchar(max)

-- @tblnameと@tblnameを参照しているVIEWを含むクエリが実行されているかの確認SQLを作成
;with objectlist as(
-- 該当オブジェクトを参照しているテーブル
select
    1 as level,
    d.referenced_id as parent_object_id,
    d.referenced_entity_name as parent_object_name,
    d.referencing_id as child_object_id,
    object_name(d.referencing_id) as child_object_name,
    o.type
from
    sys.sql_expression_dependencies as d with(nolock)
    inner join sys.objects as o with(nolock)
    on  d.referencing_id = o.object_id
    and o.type in('v')
where
    d.referenced_id = object_id(@tblname)
union all
select
    level + 1,
    c.referenced_id,
    c.referenced_entity_name,
    c.referencing_id,
    object_name(c.referencing_id) as object_name,
    o.type
from
    sys.sql_expression_dependencies as c with(nolock)
    inner join sys.objects as o with(nolock)
    on  c.referencing_id = o.object_id
    and o.type in('v')
    inner join objectlist
    on objectlist.child_object_id = c.referenced_id
)
select
    @parameter = ' and (' + 'text like ''%' + @tblname + '[^0-9|a-z|_-]%'' or ' + stuff(name,1,4,'') + ')'
from
(
    select
        coalesce(' or text like ''%' + child_object_name + '[^0-9|a-z|_-]%''','')
    from
        objectlist
    order by level asc
    for xml path('')
) as t(name)
option(maxdop 1)

declare @sql nvarchar(max) = '
select top (1) 1
from
    sys.dm_exec_requests  as er
    cross apply (select * from sys.dm_exec_cursors(er.session_id)) as a
    outer apply sys.dm_exec_sql_text(er.sql_handle) as st
    left join sys.dm_exec_sessions as es
        on er.session_id = es.session_id
where
    st.text is not null
    and er.session_id <> @@spid
    --and datediff(s, er.start_time, getdate()) >= 1 --1秒以上実行中のものに限定
'

if @parameter is null
begin
    set @parameter = ' and (' + 'text like ''%' + @tblname + '[^0-9|a-z|_-]%'')'
end
create table #tbl (val int)
--print (@sql + @parameter)


-- 該当テーブルのカーソルがオープンしてない＆ブロッキングが起きないタイミングをみつけてALTERを実行
set lock_timeout 200
declare @msg varchar(4000)

while(0=0)
begin
    begin try
    insert into #tbl
    execute (@sql + @parameter)
    if (select count(*) from #tbl) > 0
    begin
        set @msg = format(getdate(),'yyyy-mm-dd hh:mm:ss')  + ' : ' + @tblname + ' : open cursor exists.'
        raiserror(@msg,0,0) with nowait
        waitfor delay '00:00:01'
        truncate table #tbl
    end
    else
    begin
        exec ('alter table ' + @tblname + ' enable change_tracking')
        set @msg =  format(getdate(),'yyyy-mm-dd hh:mm:ss')  + ' : ' + @tblname + ' : success'
        raiserror(@msg,0,0) with nowait
        break
    end
    end try
    begin catch
        set @msg = format(getdate(),'yyyy-mm-dd hh:mm:ss')  + ' : ' + @tblname + ' : ' + cast(error_number() as varchar(100))+ ' : ' + error_message()
        raiserror(@msg,0,0) with nowait
        -- ロックタイムアウトした場合以外 (1222) は通常のエラーなのでループを抜ける
        if error_number() <> 1222
        begin
            break
        end
        waitfor delay '00:00:01'
    end catch
end

drop table #tbl