SQL Server 行ロックを取りすぎるとテーブルロックに変わる

ロックエスカレーションとは
ロックエスカレーションの発生条件
なぜロックエスカレーションさせるのか？
ロックエスカレーションによる障害事例
他DBではロックエスカレーションはあるのか？
参考

ロックエスカレーションとは

SQL Serverには注意すべき挙動として、ロックエスカレーションというものがあります。

ロックエスカレーションとは、あるテーブルに対して行ロックを取得しすぎた場合に、代わりにテーブルロックを取得する挙動です。

ロックエスカレーションの発生条件

具体的には、以下のいずれかの条件を満たす場合にロックエスカレーションが発生します

ロックメモリの閾値が40%に達する
行ロックの取得数が5000を超える

この条件を満たすとテーブルロックがかかかるようになり、そのテーブルに対する処理をブロッキングするようになり、知らぬ間に他のクエリに影響を及ぼすようになります。

なぜロックエスカレーションさせるのか？

ユーザ目線で考えると、「わざわざ行ロックを取得しているのに、なんで他のクエリにも影響するような単位のロックを勝手に取得しに行くんだよ、、」と思います。

Microsoftの公式資料によると、ロックエスカレーションを発生させる目的は「パフォーマンスの向上」「ロックメモリの調整」だそうです。

通常、SQL Server の既定の動作では、パフォーマンスが向上する場合、または過剰なシステムロックメモリをより適切なレベルに減らす必要がある場合にのみ、ロックエスカレーションが発生します。

引用：Microsoft Learn, SQL Server でロックのエスカレーションが原因で発生するブロッキング問題を解決する, https://learn.microsoft.com/ja-jp/troubleshoot/sql/database-engine/performance/resolve-blocking-problems-caused-lock-escalation

パフォーマンスについては、１テーブルに対して多くの行ロックを何度も取得するよりも、一発でテーブルロックを取得したほうがロック取得のオーバーヘッドが少なくなり性能が向上するということなのでしょうか。

またインデックスにロックをかける際にも、そのインデックスをまるごとロックしたほうがメモリ的に効率的ということもあるかもしれません。

ロックメモリの調整については、Microsoftの公式ドキュメントを参考に考えると、ロックプールがメモリの60%以上を消費しないように、N個の行ロックを1個のテーブルロックに変えることでメモリを節約しているということかもしれないですね

動的ロックプールは、データベースエンジンに割り当てられたメモリの 60% を超えるメモリを取得しません。

参考: Microsoft Learn, サーバー構成: ロック, https://learn.microsoft.com/ja-jp/sql/database-engine/configure-windows/configure-the-locks-server-configuration-option?view=sql-server-ver16

ロックエスカレーションによる障害事例

このロックエスカレーションですが、DBのスペックに余裕をもたせておいたり、トランザクションの単位を小さく保つように注意していれば、そもそも発生しないと思われますし、悪影響が及ぶものでもないと思います。

しかしながら、このロックエスカレーションによって過去にちょっとした障害が発生したことがありました。

それは「ロングトランザクションによって発生したロックエスカレーションによって他の重要なトランザクションをブロックしてしまった」というものです。

具体的には、バッチ処理でトランザクションがネストしつつ、さらにロックを開放しないまま、ロックを追加で取得するという挙動を長時間続けたことにより、テーブルロックにエスカレーションすることがありました。

結果として、そのテーブルロックによって決済システムのメインの処理である決済に失敗するということが発生しました、、、

みなさんはトランザクションあたりのロックの取得単位を小さく保ってこのような障害が発生しないように気をつけてください、、、

他DBではロックエスカレーションはあるのか？

ちなみに他のRDBであるMySQL(InnoDB)ではロックエスカレーションという挙動はありません。

ロックエスカレーション

一部のデータベースシステムで使用される操作で、多数の行ロックを単一のテーブルロックに変換し、メモリー領域を節約しますが、テーブルへの同時アクセスを削減します。

InnoDB では、行ロックに領域効率の高い表現が使用されるため、lock エスカレーションは必要ありません。

fw_error_www
fw_error_www
dev.mysql.com