SQL Server 2025 最適化されたロック

第16回 関西DB勉強会
https://kansaidbstudy.connpass.com/event/398751/

Transcript

1. SQL Server 2025 最適化されたロック 第16回 関西DB勉強会 2026/09/26 @shinsukeoda
2. アジェンダ 従来のロックの課題 何が最適化されたのか (TID ロック / LAQ) デモ (ローカル SQL Server 2025) まとめ & ベストプラクティス
3. 何が最適化された？ 「最適化されたロック」っていうけど、 何が最適化された？ 答え: 大量更新でもロックが激減する ブロッキングが減る ロックエスカレーションが起きにくくなる 仕組みを順に見ていきます
4. ロックの基本 — SQL Server はこう動く ロック = トランザクションの ACID を守る 仕組み 書き込み = X (排他) ロック、読み取り = S (共有) ロック → X と S は両立しない SQL Server の既定 (READ COMMITTED) は「ロックベース」 更新中の行は読み取りもブロックされる PostgreSQL / Oracle / MySQL (InnoDB) の MVCC (Multi-Version Concurrency Control) とはここが違う U (更新: Update) = 更新候補を調べるとき に取る SQL Server 特有のロック
5. 課題① ロックメモリ 1,000 行 UPDATE = 1,000 個の X 行 ロックをトランザクション終了まで保持 大量更新ではロックメモリが膨らむ テーブル (1,000 行を UPDATE) X X X X X X X X X X X X X X X X X X X X X X X X X X X … X 行ロック ×1,000 (コミットまで保持) ロック 1 個ごとにメモリを消費 ロックメモリが膨らむ
6. 課題② ロックエスカレーション 行ロックが増える (目安 5,000 個超) と テーブルロックに昇格 ロックをメモリで管理する SQL Server 固有の節約機構、だが… 行ロック ×5,000 超 X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X 昇格 テーブルロック ×1 → 無関係な行までブロック / 同時実行性が 低下
7. 課題③ U ロックによるブロッキング スキャン中、条件判定のために各行へ U ロックを取得 条件に合わない行を通過するだけでもブ ロックされる ※適切なインデックスがあればこの例は回 避可能 — ただし万能ではない セッション2: UPDATE … WHERE a=2 (スキャン) 行1 の U ロック取得で停止 → ブロック！ 行1 X 保持中 (セッション1) 行2 (a=2) 行3 行4
8. 最適化されたロックとは SQL Server 2025 (17.x) で追加された新機 能 Azure SQL Database / Managed Instance / Fabric では既定で有効 (常に有効) オンプレミスはデータベース単位で設定 2 つのコンポーネント TID (Transaction ID: トランザクション ID) ロック LAQ (Lock After Qualification: 修飾後ロック)
9. コンポーネント① TID ロック 各行は最後に変更したトランザクション の TID を持つ 行・ページロックは更新した瞬間に解放 → 保持は XACT への X ロック 1 個だけ 従来 X 最適化されたロック X X X X X X 行・ページロックは更新した瞬間に解放 X X X X X X X X X X X X X X XACT に X ロック ×1 保持: X 行ロック ×1,000 保持はこの 1 個だけ
10. コンポーネント② LAQ (修飾後ロック) U ロックを取らず、最新コミット済みバー ジョンで述語を評価 条件を満たした行だけ X ロックを取得 前提: RCSI (Read Committed Snapshot Isolation) セッション2: UPDATE … WHERE a=2 (スキャン) ブロックされない！ 行1 は素通り (コミット済みバージョンで判定) 行1 X 保持中 (セッション1) 行2 (a=2) X 取得して更新 行3 行4
11. LAQ の注意点 述語評価後に行が変わっていたら、再評 価してから更新 → 整合性は保たれる ただし、トランザクションの厳密な実行 順序に依存する処理では結果が変わり得 る 厳密な順序が必要なら REPEATABLE READ / SERIALIZABLE を検討
12. 有効化と前提条件 ALTER DATABASE [DB名] SET OPTIMIZED_LOCKING = ON; 前提が揃っているかは sys.databases で 確認 (デモで見せます) OPTIMIZED_LOCKING = ON RCSI (LAQ に必須) ADR (高速データベース復旧) — 必須 ↑ 土台から順に有効化する
13. デモ① ロック数の比較 1,000 行 UPDATE を OFF / ON で実行 sys.dm_tran_locks で保持ロックを観察 OFF: KEY の X ロック 1,000 個 + PAGE の IX ロック ON: XACT への X ロック 1 個だけ
14. デモ② ブロッキングの解消 (LAQ) 2 セッションで「別々の行」を UPDATE (ヒープ = テーブルスキャン) OFF: セッション 2 がブロックされる ON: ブロックされない！ 同じ行なら ON でも正しく待つ (ACID は 守られる)
15. おまけ: 新しい診断情報 新しい待機の種類: LCK_M_S_XACT_MODIFY / LCK_M_S_XACT_READ sys.dm_exec_requests の wait_resource に XACT sys.dm_tran_locks に XACT ロックリ ソース デッドロックグラフにも <xactlock> 要 素が追加
16. まとめ & ベストプラクティス 最適化されたのは「ロックの保持数・保 持期間・取得タイミング」 RCSI を有効にして使うのがベスト ロックヒント (UPDLOCK / XLOCK / HOLDLOCK …) は必要最小限に クエリの書き換えは不要 (DML の行・ ページロックにのみ影響) 参考: Microsoft Learn「最適化された ロック」