背景
変更の追跡をプロダクション環境へリリースする際に問題が生じました。
リリース自体は以下のクエリですぐに完了します。
--①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