10bet网址
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr)- 41.6Mb
PDF (A4)- 41.7Mb
Man Pages (TGZ)- 262.2Kb
Man Pages (Zip)- 372.3Kb
Info (Gzip)- 4.0Mb
Info (Zip)- 4.0Mb
Excerpts from this Manual

15.15.2.1 Using InnoDB Transaction and Locking Information

Note

This section describes locking information as exposed by the Performance Schemadata_locksanddata_lock_waitstables, which supersede theINFORMATION_SCHEMAINNODB_LOCKSandINNODB_LOCK_WAITStables in MySQL 8.0. For similar discussion written in terms of the olderINFORMATION_SCHEMAtables, seeUsing InnoDB Transaction and Locking Information, inMySQL 5.7 Reference Manual.

Identifying Blocking Transactions

It is sometimes helpful to identify which transaction blocks another. The tables that contain information aboutInnoDBtransactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. (For descriptions of these tables, seeSection 15.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.)

Suppose that three sessions are running concurrently. Each session corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these sessions have issued the following statements, but none has yet committed its transaction:

  • Session A:

    BEGIN; SELECT a FROM t FOR UPDATE; SELECT SLEEP(100);
  • Session B:

    SELECT b FROM t FOR UPDATE;
  • Session C:

    SELECT c FROM t FOR UPDATE;

In this scenario, use the following query to see which transactions are waiting and which transactions are blocking them:

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 performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

Or, more simply, use thesysschemainnodb_lock_waitsview:

SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

If a NULL value is reported for the blocking query, seeIdentifying a Blocking Query After the Issuing Session Becomes Idle.

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

In the preceding table, you can identify sessions by thewaiting queryorblocking querycolumns. As you can see:

  • Session B (trx idA4, thread6) and Session C (trx idA5, thread7) are both waiting for Session A (trx idA3, thread5).

  • Session C is waiting for Session B as well as Session A.

You can see the underlying data in theINFORMATION_SCHEMAINNODB_TRXtable and Performance Schemadata_locksanddata_lock_waitstables.

The following table shows some sample contents of theINNODB_TRXtable.

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

The following table shows some sample contents of thedata_lockstable.

lock id lock trx id lock mode lock type lock schema lock table lock index lock data
A3:1:3:2 A3 X RECORD test t PRIMARY 0x0200
A4:1:3:2 A4 X RECORD test t PRIMARY 0x0200
A5:1:3:2 A5 X RECORD test t PRIMARY 0x0200

The following table shows some sample contents of thedata_lock_waitstable.

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
Identifying a Blocking Query After the Issuing Session Becomes Idle

When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the following steps to determine the blocking query:

  1. Identify the processlist ID of the blocking transaction. In thesys.innodb_lock_waitstable, the processlist ID of the blocking transaction is theblocking_pidvalue.

  2. Using theblocking_pid, query the MySQL Performance Schemathreadstable to determine theTHREAD_IDof the blocking transaction. For example, if theblocking_pidis 6, issue this query:

    SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
  3. Using theTHREAD_ID, query the Performance Schemaevents_statements_currenttable to determine the last query executed by the thread. For example, if theTHREAD_IDis 28, issue this query:

    SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28\G
  4. If the last query executed by the thread is not enough information to determine why a lock is held, you can query the Performance Schemaevents_statements_historytable to view the last 10 statements executed by the thread.

    SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
Correlating InnoDB Transactions with MySQL Sessions

Sometimes it is useful to correlate internalInnoDBlocking information with the session-level information maintained by MySQL. For example, you might like to know, for a givenInnoDBtransaction ID, the corresponding MySQL session ID and name of the session that may be holding a lock, and thus blocking other transactions.

The following output from theINFORMATION_SCHEMAINNODB_TRXtable and Performance Schemadata_locksanddata_lock_waitstables is taken from a somewhat loaded system. As can be seen, there are several transactions running.

The followingdata_locksanddata_lock_waitstables show that:

  • Transaction华氏77度(executing anINSERT) is waiting for transactions77 e,77 d, and77Bto commit.

  • Transaction77 e(executing anINSERT) is waiting for transactions77 dand77Bto commit.

  • Transaction77 d(executing anINSERT) is waiting for transaction77Bto commit.

  • Transaction77B(executing anINSERT) is waiting for transaction77Ato commit.

  • Transaction77Ais running, currently executingSELECT.

  • TransactionE56(executing anINSERT) is waiting for transactionE55to commit.

  • TransactionE55(executing anINSERT) is waiting for transaction19Cto commit.

  • Transaction19Cis running, currently executing anINSERT.

Note

There may be inconsistencies between queries shown in theINFORMATION_SCHEMAPROCESSLISTandINNODB_TRXtables. For an explanation, seeSection 15.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.

The following table shows the contents of thePROCESSLISTtable for a system running a heavyworkload.

ID USER HOST DB COMMAND TIME STATE INFO
384 root localhost test 查询 10 update INSERT INTO t2 VALUES …
257 root localhost test 查询 3 update INSERT INTO t2 VALUES …
130 root localhost test 查询 0 update INSERT INTO t2 VALUES …
61 root localhost test 查询 1 update INSERT INTO t2 VALUES …
8 root localhost test 查询 1 update INSERT INTO t2 VALUES …
4 root localhost test 查询 0 preparing SELECT * FROM PROCESSLIST
2 root localhost test Sleep 566 NULL

The following table shows the contents of theINNODB_TRXtable for a system running a heavyworkload.

trx id trx state trx started trx requested lock id trx wait started trx weight trx mysql thread id trx query
华氏77度 LOCK WAIT 2008-01-15 13:10:16 华氏77度 2008-01-15 13:10:16 1 876 INSERT INTO t09 (D, B, C) VALUES …
77 e LOCK WAIT 2008-01-15 13:10:16 77 e 2008-01-15 13:10:16 1 875 INSERT INTO t09 (D, B, C) VALUES …
77 d LOCK WAIT 2008-01-15 13:10:16 77 d 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 …

The following table shows the contents of thedata_lock_waitstable for a system running a heavyworkload.

requesting trx id requested lock id blocking trx id blocking lock id
华氏77度 华氏77度:806 77 e 77 e:806
华氏77度 华氏77度:806 77 d 77 d:806
华氏77度 华氏77度:806 77B 77B:806
77 e 77 e:806 77 d 77 d:806
77 e 77 e:806 77B 77B:806
77 d 77 d: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

The following table shows the contents of thedata_lockstable for a system running a heavyworkload.

lock id lock trx id lock mode lock type lock schema lock table lock index lock data
华氏77度:806 华氏77度 AUTO_INC TABLE test t09 NULL NULL
77 e:806 77 e AUTO_INC TABLE test t09 NULL NULL
77 d:806 77 d AUTO_INC TABLE test t09 NULL NULL
77B:806 77B AUTO_INC TABLE test t09 NULL NULL
77B:733:12:1 77B X RECORD test t09 PRIMARY supremum pseudo-record
77A:733:12:1 77A X RECORD test t09 PRIMARY supremum pseudo-record
E56:743:6:2 E56 S RECORD test t2 PRIMARY 0, 0
E55:743:6:2 E55 X RECORD test t2 PRIMARY 0, 0
E55:743:38:2 E55 S RECORD test t2 PRIMARY 1922, 1922
19C:743:38:2 19C X RECORD test t2 PRIMARY 1922, 1922