ブロックとロックについて説明する

  • 12 分

リレーショナル データベースの特徴の 1 つにロックがあります。これは、ACID モデルの原子性、一貫性、分離性のプロパティを維持するために欠かせません。 すべての RDBMS が、データベースへの書き込みの一貫性と分離性に違反する操作をブロックします。 SQL のプログラマは、データの論理的な一貫性を確保するために、トランザクションを適切な時点で開始および終了する責任を担います。 一方、データベース エンジンに用意されているロック メカニズムも、これらのクエリの影響を受けるテーブルの論理的な一貫性を保護します。 これらの動作は、リレーショナル モデルの基礎的部分です。

SQL Server でブロックが発生するのは、あるプロセスが特定のリソース (行、ページ、テーブル、データベース) に対してロックを保持し、2 番目のプロセスが同じリソースに対して、互換性のないロックの種類のロックを取得しようとするときです。 通常、ロックの保持期間は短く、ロックを保持しているプロセスがそれを解放すると、ブロックされたプロセスはロックを取得して、そのトランザクションを完了できます。

SQL Server は、トランザクションを正常に完了するために必要な最小量のデータをロックします。 この動作により、最大限のコンカレンシーが可能になります。 たとえば、SQL Server が 1 つの行をロックしている場合、テーブル内の他のすべての行を他のプロセスが使用できるため、同時処理が可能になります。 しかし、各ロックがメモリ リソースを必要とするため、1 つのプロセスで 1 つのテーブルに対して数千もの個別のロックを保持するのはコスト効率がよくありません。 SQL Server は、コンカレンシーとコストのバランスをとろうとします。 使用される技法の 1 つに、ロックのエスカレーションというものがあります。 SQL Server は、1 つのステートメントで 1 つのオブジェクトに対して 5000 行以上のロックが必要な場合、複数の行ロックを 1 つのテーブル ロックにエスカレートします。

ロックは正常な動作であり、毎日何度も発生します。 ロックが問題になるのは、それが原因ですぐに解決できないブロックが発生するときだけです。 ブロックによって引き起こされる可能性があるパフォーマンスの問題には、次の 2 種類があります。

  • あるプロセスが一連のリソースに対してロックを保持し、それを解放するまでの期間が長い。 これらのロックは、他のプロセスのブロックの原因となり、クエリのパフォーマンスとコンカレンシーを低下させる可能性があります。

  • あるプロセスが一連のリソースに対してロックを取得し、それを解放しない。 この問題を解決するには、管理者の介入が必要です。

もう 1 つのブロックのシナリオはデッドロックです。これは、あるトランザクションがリソースに対してロックを取得しているときに、別のトランザクションが 2 番目のリソースに対してロックを取得した場合に発生します。 各トランザクションは、他のトランザクションによって現在ロックされているリソースに対してロックを取得しようとします。 理論的に、このシナリオではどちらのトランザクションも完了できないので、無限に待機することになります。 しかし SQL Server エンジンはこれらのシナリオを検出するメカニズムを備えており、デッドロックを緩和するために、ロールバックする必要のある実行済みの処理量が少ないトランザクションはどちらであるかという基準に基づいて、一方のトランザクションを強制終了します。 強制終了されるトランザクションは、デッドロックの犠牲者と呼ばれます。 デッドロックは、既定で有効になっている system_health 拡張イベント セッションに記録されます。

トランザクションの概念を理解しておくことは重要です。 オートコミット モードが SQL Server と Azure SQL Database の既定のモードです。これは、下のステートメントによって行われた変更は自動的にデータベースのトランザクション ログに記録されることを意味します。

INSERT INTO DemoTable (A) VALUES (1);

開発者がアプリケーション コードをより細かく制御できるようにするために、SQL Server ではトランザクションを明示的に制御することもできます。 下のクエリは、DemoTable テーブルの行に対するロックを取得し、そのロックは、トランザクションをコミットするための後続のコマンドが追加されるまで解放されません。

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

上のクエリを記述する正しい方法は次のとおりです。

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

COMMIT TRANSACTION コマンドは、変更のレコードをトランザクション ログに明示的にコミットします。 変更されたデータは、最終的に非同期でデータ ファイルに格納されます。 これらのトランザクションは、データベース エンジンにとって作業単位となります。 開発者が COMMIT TRANSACTION コマンドを発行し忘れると、トランザクションは開いたままになり、ロックは解放されません。 これが、トランザクションが長時間実行される主な理由の 1 つです。

データベースのコンカレンシーを支援するためにデータベース エンジンが使用するもう 1 つのメカニズムは、行のバージョン管理です。 行のバージョン管理の分離レベルがデータベースで有効になっている場合、エンジンは変更された各行のバージョンを TempDB に保持します。 これは通常、データベースへの書き込みを行っているクエリが読み取りクエリによってブロックされるのを防ぐために、混合使用ワークロードで使用されます。

コミットまたはロールバックを待っている、開かれているトランザクションを監視するには、次のクエリを実行します。

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

分離レベル

SQL Server にはいくつかの分離レベルが用意されており、データの一貫性と正確性を保証するために必要なレベルを定義できます。 分離レベルによって、コンカレンシーと一貫性のバランスを取ることができます。 分離レベルは、データの変更を防ぐために取得されるロックには影響しません。トランザクションは、変更対象のデータに対して常に排他的ロックを取得します。 しかし、分離レベルは、ロックが保持される時間の長さに影響を与える可能性があります。 分離レベルが低いと、複数のユーザー プロセスが同時にデータにアクセスできるようになりますが、データの一貫性が損なわれるリスクが高まります。 SQL Server の分離レベルを以下に示します。

  • Read uncommitted – 使用できる最も低い分離レベルです。 ダーティ リードが許可されています。これは、あるトランザクションが、まだコミットされていない別のトランザクションによって行われた変更を認識する可能性があることを意味します。

  • Read committed – あるトランザクションが、他のトランザクションによって以前に読み取られたが変更されていないデータを、最初のトランザクションが終了するのを待たずに読み取ることができます。 また、このレベルでは、選択操作が実行されると同時に読み取りロックが解放されます。 これは、既定の SQL Server レベルです。

  • Repeatable Read – このレベルでは、選択したデータに対して取得された読み取りおよび書き込みロックが、トランザクションが終了するまで保持されます。

  • Serializable – これは、トランザクションが分離される最も高い分離レベルです。 選択されたデータに対して読み取りと書き込みのロックが取得され、トランザクションが終了するまで解放されません。

また、SQL Server には、行のバージョン管理を含む 2 つの分離レベルがあります。

  • Read Committed Snapshot – このレベルでは、読み取り操作は行やページのロックを取得せず、エンジンは各操作に対して、クエリの開始時に存在していたデータとの整合性があるスナップショットを提示します。 このレベルは通常、ユーザーが OLTP データベースに対して頻繁にレポート クエリを実行する場合に、読み取り操作が書き込み操作をブロックするのを防ぐために使用されます。

  • Snapshot – このレベルでは、行のバージョン管理によってトランザクション レベルの読み取りの一貫性が確保されます。 このレベルは、更新の競合に対して脆弱です。 このレベルで実行しているトランザクションが別のトランザクションによって変更されたデータを読み取ると、スナップショット トランザクションによる更新が終了してロールバックされます。 これは、Read Committed Snapshot 分離では問題ではありません。

分離レベルは、次に示すように、T-SQL SET コマンドを使用して各セッションに設定されます。

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

グローバル分離レベルを、データベースで実行しているすべてのクエリ、あるいは特定のユーザーによって実行されるすべてのクエリに対して設定する方法はありません。 これはセッション レベルの設定です。

ブロックの問題の監視

ブロックの問題は散発的な性質を持つため、その特定は困難です。 sys.dm_tran_locks という名前の DMV があり、これを sys.dm_exec_requests と組み合わせることで、各セッションが保持しているロックに関する詳細情報を得ることができます。 拡張イベン ト エンジンを使用してそれを継続的に行うことが、ブロックの問題を監視するためのよりよい方法になります。

ブロックの問題は、通常、次の 2 つのカテゴリに分類されます。

  • 不適切なトランザクション設計。 上記のように、COMMIT TRANSACTION がないトランザクションはいつまでも終了しません。 これはシンプルな例ですが、1 つのトランザクションで多すぎる処理を行おうとしたり、リンク サーバー接続を使用する分散トランザクションを行ったりすると、予測不可能なパフォーマンスが発生する可能性があります。

  • スキーマ設計に起因する長時間実行トランザクション。 この例としてよくあるのが、インデックスが存在しない列の更新や、設計が不適切な更新クエリです。

ロックに関連するパフォーマンスの問題を監視することで、ロックに関連するパフォーマンスの低下をすばやく特定できます。

ブロックを監視する方法の詳細については、「SQL Server のブロックの問題を理解および解決する」を参照してください。