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

12 分

ロックはリレーショナルデータベースの重要な機能であり、ACID モデルの原子性、一貫性、分離プロパティを維持するために不可欠です。すべての RDBMS は、データベース書き込みの整合性と分離に違反するアクションをブロックします。 SQL プログラマは、データの一貫性を確保するために、適切なポイントでトランザクションを開始および終了する必要があります。データベースエンジンは、影響を受けるテーブルの論理整合性を保護するためのロックメカニズムを提供します。これはリレーショナルモデルの基礎となります。

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

SQL Server は、トランザクションを完了するために必要な最小量のデータをロックし、最大コンカレンシーを可能にします。たとえば、SQL Server で 1 つの行がロックされている場合、テーブル内の他のすべての行は他のプロセスで引き続き使用でき、同時作業が可能になります。ただし、各ロックにはメモリリソースが必要であるため、1 つのプロセスで 1 つのテーブルに対して何千もの個別のロックを保持することはコスト効率が高くはありません。コンカレンシーとコストのバランスを取るために、SQL Server はロックエスカレーションと呼ばれる手法を使用します。 1 つのオブジェクトで 5,000 行を超える行を 1 つのステートメントでロックする必要がある場合、SQL Server は複数の行ロックを 1 つのテーブルロックにエスカレートします。

ロックは通常の動作であり、1 日を通して頻繁に発生します。ブロックが発生してすぐに解決されない場合にのみ問題になります。ブロックによって発生するパフォーマンスの問題には、次の 2 種類があります。

プロセスは、リソースのセットを解放する前に、一連のリソースに対するロックを長時間保持し、他のプロセスがクエリのパフォーマンスとコンカレンシーをブロックおよび低下させます。
プロセスは、一連のリソースに対するロックを取得し、それを解放せずに放置されるため、管理者の介入が必要となります。

デッドロックとは、あるトランザクションがリソースのロックを保持し、別のトランザクションが別のリソースに対してロックを保持している場合に発生する、もう 1 つのブロックシナリオです。その後、各トランザクションは、他のトランザクションによって現在ロックされているリソースのロックを取得しようとします。これにより、どちらのトランザクションも完了できないので、無限待機が発生します。 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 – 使用できる最も低い分離レベルです。ダーティ読み取りは許可されます。つまり、1 つのトランザクションで、まだコミットされていない別のトランザクションによって行われた変更が表示される可能性があります。
コミットされたデータの読み取り – トランザクションは、最初のトランザクションが完了するのを待たずに、別のトランザクションで変更されていない以前に読み取ったデータを読み取ることができます。このレベルでは、選択操作が実行されるとすぐに読み取りロックも解放されます。これが既定の SQL Server レベルです。
反復可能読み取り – このレベルでは、選択したデータに対して取得された読み取りと書き込みのロックが、トランザクションの終了まで保持されます。
Serializable – これは、トランザクションが分離される最も高い分離レベルです。読み取りと書き込みのロックは、選択したデータに対して取得され、トランザクションの終了まで解放されません。

SQL Server には、行のバージョン管理を含む 2 つの分離レベルも含まれています。

コミットされたスナップショットの読み取り – このレベルでは、読み取り操作は行またはページロックを行いません。エンジンは、クエリの開始時に存在していたデータの一貫性のあるスナップショットを各操作に提示します。通常、このレベルは、読み取り操作によって書き込み操作がブロックされないようにするために、ユーザーが OLTP データベースに対して頻繁にレポートクエリを実行する場合に使用されます。
Snapshot – このレベルでは、行のバージョン管理によってトランザクションレベルの読み取りの一貫性が確保されます。このレベルは、更新の競合に対して脆弱です。このレベルで実行されているトランザクションが別のトランザクションによって変更されたデータを読み取ると、スナップショットトランザクションによる更新が終了し、ロールバックされます。これは、Read Committed Snapshot 分離では問題ではありません。

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

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

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

ブロックの問題の監視

ブロックする問題の特定は、散発的な性質のために困難な場合があります。 DMV sys.dm_tran_locksは、 sys.dm_exec_requestsと結合すると、各セッションによって保持されるロックに関する情報を提供します。ブロックの問題を監視するより効果的な方法は、拡張イベントエンジンを継続的に使用することです。

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

不適切なトランザクション設計: たとえば、 COMMIT TRANSACTION のないトランザクションは終了しません。 1 つのトランザクションで多くの作業を実行しようとしたり、リンクサーバー接続を使用して分散トランザクションを実行しようとすると、予期しないパフォーマンスが発生する可能性があります。
スキーマの設計に起因する実行時間の長いトランザクション: 多くの場合、インデックスが見つからない列または不適切に設計された更新クエリを含む列の更新が含まれます。

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

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

フィードバック

このページはお役に立ちましたか?