MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.14.2.1 InnoDB トランザクションおよびロックテーブルの使用例

MySQL 5.6 リファレンスマニュアル / ... / InnoDB トランザクションおよびロックテーブルの使用例

14.14.2.1 InnoDB トランザクションおよびロックテーブルの使用例

例 14.11 ブロックしているトランザクションの識別

別のトランザクションをどのトランザクションがブロックしているかを識別できると役立つ場合があります。INFORMATION_SCHEMA テーブルを使用すると、別のトランザクションをどのトランザクションが待機しているかや、どのリソースが要求されているかを見つけることができます。

3 人のユーザーが同時に実行している次のシナリオがあるとします。各ユーザー (またはセッション) は MySQL スレッドに対応し、あるトランザクションを別のトランザクションのあとに実行します。これらのユーザーが次のコマンドを発行したが、まだだれも自分のトランザクションをコミットしていないときのシステムの状態を考えてみてください。

ユーザー A:

BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);

ユーザー B:
```
SELECT b FROM t FOR UPDATE;
```
ユーザー C:
```
SELECT c FROM t FOR UPDATE;
```

このシナリオでは、次のクエリーを使用して、だれがだれを待機しているかを確認できます。

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;

waiting trx id	waiting thread	waiting query	blocking trx id	blocking thread	blocking query
`A4`	`6`	`SELECT b FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A4`	`6`	`SELECT b FROM t FOR UPDATE`

上の結果では、「waiting query」または「blocking query」でユーザーを識別できます。次のことがわかります。

ユーザー B (trx id 'A4'、スレッド 6) とユーザー C (trx id 'A5'、スレッド 7) はどちらも、ユーザー A (trx id 'A3'、スレッド 5) を待機しています。
ユーザー C は、ユーザー A のほかにユーザー B を待機しています。

テーブル INNODB_TRX、INNODB_LOCKS、および INNODB_LOCK_WAITS 内のベースとなるデータを確認できます。

次の表は、INFORMATION_SCHEMA.INNODB_TRX の内容のいくつかのサンプルを示しています。

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
`A3`	`RUNNING`	`2008-01-15 16:44:54`	`NULL`	`NULL`	`2`	`5`	`SELECT SLEEP(100)`
`A4`	`LOCK WAIT`	`2008-01-15 16:45:09`	`A4:1:3:2`	`2008-01-15 16:45:09`	`2`	`6`	`SELECT b FROM t FOR UPDATE`
`A5`	`LOCK WAIT`	`2008-01-15 16:45:14`	`A5:1:3:2`	`2008-01-15 16:45:14`	`2`	`7`	`SELECT c FROM t FOR UPDATE`

次の表は、INFORMATION_SCHEMA.INNODB_LOCKS の内容のいくつかのサンプルを示しています。

lock id	lock trx id	lock mode	lock type	lock table	lock index	lock space	lock page	lock rec	lock data
`A3:1:3:2`	`A3`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`0x0200`
`A4:1:3:2`	`A4`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`0x0200`
`A5:1:3:2`	`A5`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`0x0200`

次の表は、INFORMATION_SCHEMA.INNODB_LOCK_WAITS の内容のいくつかのサンプルを示しています。

requesting trx id	requested lock id	blocking trx id	blocking lock id
`A4`	`A4:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A4`	`A4:1:3:2`

例 14.12 情報スキーマテーブル内のトランザクションデータのより複雑な例

場合によっては、内部の InnoDB ロック情報を MySQL で保持されているセッションレベルの情報と関連付けたいことがあります。たとえば、特定の InnoDB トランザクション ID について、ロックを保持しているために別のトランザクションをブロックしている可能性があるユーザーの対応する MySQL セッション ID と名前を知りたいことがあります。

INFORMATION_SCHEMA テーブルからの次の出力は、ある程度負荷の高いシステムから取得されました。

次の表からわかるように、実行中のトランザクションが複数存在します。

次の INNODB_LOCKS および INNODB_LOCK_WAITS テーブルは、次のことを示しています。

トランザクション 77F (INSERT を実行中) は、トランザクション 77E、77D、および 77B がコミットするのを待機しています。
トランザクション 77E (INSERT を実行中) は、トランザクション 77D および 77B がコミットするのを待機しています。
トランザクション 77D (INSERT を実行中) は、トランザクション 77B がコミットするのを待機しています。
トランザクション 77B (INSERT を実行中) は、トランザクション 77A がコミットするのを待機しています。
トランザクション 77A は実行中であり、現在 SELECT を実行しています。
トランザクション E56 (INSERT を実行中) は、トランザクション E55 がコミットするのを待機しています。
トランザクション E55 (INSERT を実行中) は、トランザクション 19C がコミットするのを待機しています。
トランザクション 19C は実行中であり、現在 INSERT を実行しています。

2 つのテーブル INNODB_TRX.TRX_QUERY と PROCESSLIST.INFO に示されているクエリー間に不整合が存在する可能性があることに注意してください。いずれかの特定のスレッドについて、そのスレッドの現在のトランザクション ID や、そのトランザクションで実行されているクエリーがこれらの 2 つのテーブルで異なる可能性があります。説明については、セクション14.14.2.3.1「PROCESSLIST データとの不整合の可能性」を参照してください。

次の表は、重いワークロードを実行しているシステム内の INFORMATION_SCHEMA.PROCESSLIST の内容を示しています。

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
`384`	`root`	`localhost`	`test`	`Query`	`10`	`update`	`insert into t2 values …`
`257`	`root`	`localhost`	`test`	`Query`	`3`	`update`	`insert into t2 values …`
`130`	`root`	`localhost`	`test`	`Query`	`0`	`update`	`insert into t2 values …`
`61`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`insert into t2 values …`
`8`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`insert into t2 values …`
`4`	`root`	`localhost`	`test`	`Query`	`0`	`preparing`	`SELECT * FROM processlist`
`2`	`root`	`localhost`	`test`	`Sleep`	`566`		`NULL`

次の表は、重いワークロードを実行しているシステム内の INFORMATION_SCHEMA.INNODB_TRX の内容を示しています。

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
`77F`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77F`:806	`2008-01-15 13:10:16`	`1`	`876`	`insert into t09 (D, B, C) values …`
`77E`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77E`:806	`2008-01-15 13:10:16`	`1`	`875`	`insert into t09 (D, B, C) values …`
`77D`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77D`:806	`2008-01-15 13:10:16`	`1`	`874`	`insert into t09 (D, B, C) values …`
`77B`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77B`:733:12:1	`2008-01-15 13:10:16`	`4`	`873`	`insert into t09 (D, B, C) values …`
`77A`	`RUNNING`	`2008-01-15 13:10:16`	`NULL`	`NULL`	`4`	`872`	`select b, c from t09 where …`
`E56`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E56`:743:6:2	`2008-01-15 13:10:06`	`5`	`384`	`insert into t2 values …`
`E55`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E55`:743:38:2	`2008-01-15 13:10:13`	`965`	`257`	`insert into t2 values …`
`19C`	`RUNNING`	`2008-01-15 13:09:10`	`NULL`	`NULL`	`2900`	`130`	`insert into t2 values …`
`E15`	`RUNNING`	`2008-01-15 13:08:59`	`NULL`	`NULL`	`5395`	`61`	`insert into t2 values …`
`51D`	`RUNNING`	`2008-01-15 13:08:47`	`NULL`	`NULL`	`9807`	`8`	`insert into t2 values …`

次の表は、重いワークロードを実行しているシステム内の INFORMATION_SCHEMA.INNODB_LOCK_WAITS の内容を示しています。

requesting trx id	requested lock id	blocking trx id	blocking lock id
`77F`	`77F`:806	`77E`	`77E`:806
`77F`	`77F`:806	`77D`	`77D`:806
`77F`	`77F`:806	`77B`	`77B`:806
`77E`	`77E`:806	`77D`	`77D`:806
`77E`	`77E`:806	`77B`	`77B`:806
`77D`	`77D`:806	`77B`	`77B`:806
`77B`	`77B`:733:12:1	`77A`	`77A`:733:12:1
`E56`	`E56`:743:6:2	`E55`	`E55`:743:6:2
`E55`	`E55`:743:38:2	`19C`	`19C`:743:38:2

次の表は、重いワークロードを実行しているシステム内の INFORMATION_SCHEMA.INNODB_LOCKS の内容を示しています。

lock id	lock trx id	lock mode	lock type	lock table	lock index	lock space	lock page	lock rec	lock data
`77F`:806	`77F`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77E`:806	`77E`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77D`:806	`77D`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77B`:806	`77B`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77B`:733:12:1	`77B`	`X`	`RECORD`	`test`.`t09`	`PRIMARY`	`733`	`12`	`1`	`supremum pseudo-record`
`77A`:733:12:1	`77A`	`X`	`RECORD`	`test`.`t09`	`PRIMARY`	`733`	`12`	`1`	`supremum pseudo-record`
`E56`:743:6:2	`E56`	`S`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`6`	`2`	`0, 0`
`E55`:743:6:2	`E55`	`X`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`6`	`2`	`0, 0`
`E55`:743:38:2	`E55`	`S`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`38`	`2`	`1922, 1922`
`19C`:743:38:2	`19C`	`X`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`38`	`2`	`1922, 1922`

PREV HOME UP NEXT