SQL ステートメントで MariaDB のロック情報を掘り下げる：INNODB_LOCK_WAITS テーブルの活用法

MariaDBのInformation Schema INNODB_LOCK_WAITSテーブルは、ブロックされているInnoDBトランザクションに関する情報を提供します。各行は、ブロックされているトランザクションと、そのトランザクションをブロックしているロックを示します。

このテーブルは、以下の状況を特定するのに役立ちます。

• パフォーマンスの低下：ロック待機が原因で、アプリケーションのパフォーマンスが低下する場合があります。
• デッドロック：互いにブロックし合っているトランザクションが存在する場合に発生します。
• ロック競合：複数のトランザクションが同じデータレコードにアクセスしようとしている場合に発生します。

列の説明

列名	説明	データ型
REQUESTING_TRX_ID	ロックを要求しているトランザクションID	BIGINT
REQUESTING_ENGINE_LOCK_ID	トランザクションによって要求されたロックID	BIGINT
REQUESTING_LOCK_MODE	トランザクションによって要求されたロックモード	VARCHAR(14)
REQUESTING_TABLE_NAME	トランザクションによってロックされたテーブルの名前	VARCHAR(64)
REQUESTING_INDEX_NAME	トランザクションによってロックされたインデックスの名前	VARCHAR(64)
REQUESTING_SPACE_ID	トランザクションによってロックされたテーブルスペースID	BIGINT
REQUESTING_PAGE_NO	トランザクションによってロックされたレコードページ番号	BIGINT
REQUESTING_REC_NO	トランザクションによってロックされたレコードヒープ番号	BIGINT
REQUESTING_DATA	トランザクションによってロックされたレコードの主キー値	VARCHAR(255)
BLOCKING_TRX_ID	ロックを保持しているトランザクションID	BIGINT
BLOCKING_ENGINE_LOCK_ID	トランザクションによって保持されているロックID	BIGINT
BLOCKING_LOCK_MODE	トランザクションによって保持されているロックモード	VARCHAR(14)
BLOCKING_TABLE_NAME	トランザクションによって保持されているロックのテーブル名	VARCHAR(64)
BLOCKING_INDEX_NAME	トランザクションによって保持されているロックのインデックス名	VARCHAR(64)
BLOCKING_SPACE_ID	トランザクションによって保持されているロックのテーブルスペースID	BIGINT
BLOCKING_PAGE_NO	トランザクションによって保持されているロックのレコードページ番号	BIGINT
BLOCKING_REC_NO	トランザクションによって保持されているロックのレコードヒープ番号	BIGINT
BLOCKING_DATA	トランザクションによって保持されているロックのレコードの主キー値	VARCHAR(255)
SQL_STATEMENT	ロックを待機しているトランザクションで実行されているSQLステートメント	VARCHAR(255)
WAITING_TRX_ID	ロックを待機しているトランザクションID	BIGINT
WAITING_ENGINE_LOCK_ID	トランザクションによって要求されたロックID	BIGINT
WAITING_LOCK_MODE	トランザクションによって要求されたロックモード	VARCHAR(14)
WAITING_TABLE_NAME	トランザクションによってロックされたテーブルの名前	VARCHAR(64)
WAITING_INDEX_NAME	トランザクションによってロックされたインデックスの名前	VARCHAR(64)
WAITING_SPACE_ID	トランザクションによってロックされたテーブルスペースID	BIGINT
WAITING_PAGE_NO	トランザクションによってロックされたレコードページ番号	BIGINT
WAITING_REC_NO	トランザクションによってロックされたレコードヒープ番号	BIGINT
WAITING_DATA	トランザクションによってロックされたレコードの主キー値	VARCHAR(255)
SCHEMA_NAME	ロックが発生したスキーマの名前	VARCHAR(64)

ロックモード

以下のロックモードがサポートされています。

• S_GAP: 共有ギャップロック。将来の共有ロックを取得するためのロックです。
• IX: 意図排他ロック。将来の排他ロックと共有ロックを取得するためのロックです。
• IS: 意図共有ロック。将来の排他ロックを取得するためのロックです。
• X: 排他ロック。他のトランザクションによる読み取りアクセスと書き込みアクセスをブロックします。
• S: 共有ロック。他のトランザクションによる読み取りアクセスを許可します。

---

-- ロック待機中のトランザクションを表示
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 特定のトランザクションIDでブロックされているトランザクションを表示
SELECT * FROM information_schema.INNODB_LOCK_WAITS WHERE REQUESTING_TRX_ID = 1234;

-- 特定のテーブルでブロックされているトランザクションを表示
SELECT * FROM information_schema.INNODB_LOCK_WAITS WHERE REQUESTING_TABLE_NAME = 'mytable';

-- ロック待機中のトランザクションと、そのトランザクションを実行しているセッションに関する情報を表示
SELECT iw.*, s.USER, s.HOST, s.ID
FROM information_schema.INNODB_LOCK_WAITS iw
JOIN processlist s ON iw.WAITING_TRX_ID = s.ID;

• ロック情報は動的に更新されるため、結果は常に最新ではない場合があります。
• このテーブルは、InnoDBストレージエンジンを使用するテーブルにのみ適用されます。
• PROCESS権限が必要です。

• InnoDBのロックメカニズムの詳細については、MariaDBのドキュメントを参照してください。
• ロック競合やデッドロックを解決するには、アプリケーションコードを修正する必要がある場合があります。

---

• data_lock_waits テーブル: ロック待機中のトランザクションに関する情報を提供します。
• data_locks テーブル: ロックに関する情報を提供します。

これらのテーブルは、INNODB_LOCK_WAITS テーブルよりも以下の点で優れています。

• 永続性: ロック情報は永続的に保存されるため、問題を分析するのに役立ちます。
• より高いパフォーマンス: INNODB_LOCK_WAITS テーブルよりも効率的に更新されます。
• より詳細な情報: ロックの種類、待機時間、ロック待機中のトランザクションのスレッドIDなど、より詳細な情報を提供します。

data_locks および data_lock_waits テーブルの使用例

以下の例では、data_locks テーブルを使用して、現在保持されているすべてのロックを表示する方法を示します。

SELECT * FROM performance_schema.data_locks;

以下の例では、data_lock_waits テーブルを使用して、ロック待機中のすべてのトランザクションを表示する方法を示します。

SELECT * FROM performance_schema.data_lock_waits;

• MyBatisなどのORM: 一部のORMは、ロック情報に関する情報を提供する機能を備えています。
• InnoDB Monitor: InnoDB Monitorは、MySQL 5.6以降で使用できるツールで、ロック情報を含むパフォーマンスデータを表示できます。

• InnoDBのロックメカニズムの詳細については、MySQLのドキュメントを参照してください。
• ロック競合やデッドロックを解決するには、アプリケーションコードを修正する必要がある場合があります。
• MySQL 8.0以降を使用している場合は、INNODB_LOCK_WAITS テーブルではなく、data_locks および data_lock_waits テーブルを使用してください。