SQL Server のロール設定をした際のロック競合とデッドロックについての調査をしていた際に見つけた情報として、次のようなものがありました。
- SQL Server Deadlock on subresource PERMISSIONS when sp_executesql with #temptables
- High volume of deadlocks with Vault in Sql server
どちらも同一の情報がベースとなっており、HashiCorp の Vault と SQL Server の組み合わせた場合のデッドロックの発生となるようですが、情報としてはとても興味深い内容となっていました。
ロール設定時に取得されるロック
SQL Server のロール設定を行った場合、該当のプリンシパル (ロール) に対して、瞬間的に SCH_M (スキーマ修正) ロックが取得されます。
exec sp_addrolemember 'db_datareader' , 'testlogin0001'
このようなクエリを実行した場合は次のような SCH_M のロックが取得されます。
- principal_id = 16390 : db_datareader
- principal_id = 8 : testlogin0001
となるのですが、操作しているロール / ログインに対してロックの取得が行われていますね。
SCH_M なので、該当のロール / ユーザーに対して SCH_S が取得されているとロック競合が発生します。
DB 操作時に取得されるロック
DB 操作時にですが操作の初期フェーズとして、「FCheckDbAccess」のトランザクションとして、ユーザーにどのような権限が付与されているかの取得が行われているようで、その中でロールの情報の取得も行われているようです。(この辺の処理は、「sqlmin!CSECCache～」の処理で実行されているように見えました)
この際、「自分のプリンシパルの情報の取得」「自分がメンバーとして設定されているロールの取得」も行われているようで、該当の情報に対して SCH_M のロックが取得されている場合は、DB の操作が待機されます。
sp_addrolemember はトランザクション内で実行できるので、次のような書き方も行うことができます。
BEGIN TRAN exec sp_addrolemember 'db_datareader' , 'testlogin0001'
この状態 (トランザクションを確定していない) で、ロールの変更を実施している DB に対して testlogin0001 で接続をしようとすると、次のようなロック競合が発生します。
ロック競合が発生しているのが確認できますね。
次に「testlogin0001」のユーザーが「db_datareader 」のメンバーとなっている状態で次のクエリを実行してみます。
今回のクエリは、db_datareader に対して testlogin0002 を追加するものとなっているため、testlogin0001 は直接操作をしていません。
BEGIN TRAN exec sp_addrolemember 'db_datareader' , 'testlogin0002'
この場合、testlogin0001 で該当の DB にアクセスをすると、「db_datareader」のプリンシパルの情報取得でロック競合が発生します。
現在は「testlogin0001」が「db_datareader」のメンバーとなっているのでこのような競合が発生しますが、「db_datareader」のメンバーから抜けた状態であれば、ロック競合は発生しません。「自分がメンバーとなっているロールに対して SCH_M が取得されている」場合は競合が発生するというようですね。
まとめ
意識しないところで METADATA のロックとして、プリンシパルの情報の取得が行われており、操作によってはロック競合が発生する可能性があります。
冒頭で紹介した情報では、SET LOCK_TIMEOUT を 0 にして、ロック競合が発生したらすぐにタイムアウトするようにしています
SCH_M のロックは強力なロックとなるため、同時実行性を低下させる可能性が高くなります。LOCK_TIMEOUT を設定してロック競合が発生したらすぐにエラーとするような考慮は重要ですね。
ステートメントによっては、内部でトランザクションを開いているケースもありますので、@@TRANCOUNT で開いているトランザクションを確認し 0 になるまでロールバックを繰り返す、SET XACT_ABORT を ON にして、トランザクションのロールバックを実行するというようなことを合わせて考慮するとよいかもしれません。