例 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
の内容のいくつかのサンプルを示しています。
例 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 |