まえがき
本記事は、SQL Serverで発生した「syscolpars が関与するデッドロック」について、実際の現象をベースに調査・考察した内容を記録したものです。
発生メカニズムの一部は推測を含みますが、同様のトラブルに遭遇した方の参考になればと思います。
前提
今回のデッドロックを把握するために必要な前提情報を記載します。
- SQL Server 2022
- Read Committed Snapshot Isolation（RCSI）：ON
- スナップショット分離：有効
調査の背景
今回のデッドロックは、「トランザクション内でシステムカタログ（sys）を参照し、その結果から主キーを特定して、キーをもとに対象のテーブルを更新する」という処理同士で発生しました。
具体的には下記のような流れで処理が実行されていました。
- システムカタログから主キー列名を取得
- 取得した主キー列を使用して更新クエリを生成して実行
- 1、2をループで繰り返す
--実処理を抽象化したサンプルクエリです
--対象テーブル一覧を取得し、PKカラムを取得→動的更新クエリを実行する
DECLARE @TableName nvarchar(128)
DECLARE @KeyColumnName nvarchar(128)
DECLARE @SQL nvarchar(MAX)
DECLARE CUR_Table CURSOR FOR
SELECT TableName
FROM TargetTables
OPEN CUR_Table
FETCH NEXT FROM CUR_Table INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1
@KeyColumnName = c.name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.tables t
ON t.object_id = i.object_id
WHERE i.is_primary_key = 1
AND t.name = @TableName
SET @SQL =
N'UPDATE ' + QUOTENAME(@TableName) +
N' SET Value = ''X'' WHERE ' + QUOTENAME(@KeyColumnName) + N' = 1'
EXEC(@SQL)
/* INSERTによる更新処理もここで実行 */
FETCH NEXT FROM CUR_Table INTO @TableName
END
CLOSE CUR_Table
DEALLOCATE CUR_Table
デッドロックのログ内で sys.syscolpars に対してSロックとXロックの競合が確認できたため、その発生要因を特定するべく調査を行いました。
そもそもsyscolparsとは？
テーブルの列情報を内部的に管理しているシステムカタログです。
sys.columnsの更に下にある実体のようです。
MS公式の記事によると下記です。
すべてのデータベースに存在します。 テーブル、ビュー、またはテーブル値関数のすべての列の行を格納します。
また、プロシージャまたは関数のパラメーターごとの行も格納します
SQL Server の中でも公開されていない内部オブジェクトであるため、通常の接続ではアクセスすることができません。
Dedicated Administrator Connection（DAC）接続を通じてのみ参照することができます。
DAC接続の方法については参考1を参照してください。
調査したこと
➀デッドロックグラフの確認
取得したデッドロックグラフは下記です。
※DB名はマスキングしています。
グラフから syscolpars へのSロック/Xロックが相互待ち状態となっていることが分かります。
キーロック同士のデッドロックのため、同一テーブル内でロック粒度が大きいことによるデッドロックではないことが予想されます。
なぜシステムカタログへの参照でSロックが保持されていたのかを明らかにするため、システムカタログ参照時のロック動作を確認しました。
②システムカタログ参照時のロック動作確認
まず、対象の処理と同様に sys.indexes/sys.columns 等をトランザクション内で参照した場合にどのようなロックが取得されるのかを検証しました。
--用意したテーブル
CREATE TABLE [dbo].[FugaTable]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[Data] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY]
--用意したデータ
DECLARE @i INT = 1
WHILE @i <= 10000
BEGIN
INSERT INTO FugaTable
(
Name
,Data
)
VALUES
(
CONCAT('Name_', @i),
CONCAT('Data_', @i)
)
SET @i += 1
END
検証では、下記のようにトランザクションを開始した状態で、システムカタログを参照し、その間にロック情報を確認しました。
BEGIN TRAN
SELECT *
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.tables t
ON t.object_id = i.object_id
WHERE i.is_primary_key = 1
AND t.name = 'FugaTable'
--ROLLBACK
しかし、結果として取得されたロックは DATABASE の Sロック程度であり、syscolparsに対するロックは確認できませんでした。
--ロック情報確認用クエリ
--DAC接続をして実行する
SELECT
l.request_session_id,
l.resource_type,
l.resource_associated_entity_id,
l.request_mode,
l.request_status,
o.name
FROM sys.dm_tran_locks l
LEFT JOIN sys.objects o
ON l.resource_associated_entity_id = o.object_id
ORDER BY request_session_id
③テーブル更新時のロックの動作確認
次に、実際にテーブルを更新した場合に、システムカタログ（特に syscolpars）に対してどのようなロックが取得されるかを確認しました。
検証に使用したクエリは以下です。
BEGIN TRAN
DECLARE @i INT = 1
WHILE @i <= 1000
BEGIN
INSERT INTO FugaTable
(
Name
,Data
)
VALUES
(
CONCAT('Name_', @i),
CONCAT('Data_', @i)
)
SET @i += 1
END
--ROLLBACK
下記のようにsyscolparsのIXロックを確認することができました。
更新件数を減らして確認した結果が下記です。
| INSERT件数 | syscolpars への発生有無 |
|---|---|
| 1000 件 | 発生あり |
| 529 件 | 発生あり |
| 528 件 | 発生なし |
考察
今回の検証から、システムカタログを参照する処理では、トランザクション内でsyscolparsのロックは確認できませんでした。
一方、大量の INSERT を実行した場合には syscolpars に対してIXロックが発生することが確認できました。特に、INSERT 件数が一定の閾値を超えた段階からロックが発生していたことから、統計情報の自動更新がトリガーになっている可能性が高いものと推測しています。
デッドロックグラフを改めて確認すると、syscolparsへのSロック要求が発生していることが読み取れます。
つまり、今回のデッドロックは「システムカタログへの参照処理がSロックを長時間保持していたために発生した」のではなく、システムカタログへアクセスする内部処理の中で “瞬間的にSロックを取得しようとするタイミング” が存在し、それが統計情報の自動更新によって保持されていたXロックと衝突したものと予想されます。
下記の図のようなイメージです。
まとめ
同一トランザクション内でシステムカタログの参照処理と更新処理が混在すると、統計情報更新のタイミングと衝突してデッドロックとなる可能性があることが分かりました。
本件では、クエリ内でシステムカタログを参照せず、主キー情報を別テーブルで管理する方式に変更することで対応しましたが、設計次第ではキャッシュ化・事前取得・トランザクション外への切り出しなど、複数の回避策が考えられるかと思います。
本記事が同様の事象に悩む方の参考になれば幸いです。
補足
MS記事に記載の統計情報更新の閾値と今回の検証値は一致していませんでしたが、統計更新は修正件数だけでなくクエリ最適化の判断や実行タイミングにも依存するため、内部の発動タイミングを厳密に推定することは困難だと思っています。
今回の調査では、syscolparsへのIXロックの発生タイミングから「統計情報更新が起きていた可能性が高い」と判断しています。