Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.7Mb
PDF (A4) - 34.7Mb
PDF (RPM) - 32.6Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.3Mb
HTML Download (RPM) - 7.1Mb
Man Pages (TGZ) - 129.9Kb
Man Pages (Zip) - 185.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb


Pre-General Availability Draft: 2018-01-12

15.14.2.1 Using InnoDB Transaction and Locking Information

Note

This section describes locking information as exposed by the Performance Schema data_locks and data_lock_waits tables, which supersede the INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables in MySQL 8.0. For similar discussion written in terms of the older INFORMATION_SCHEMA tables, see Using InnoDB Transaction and Locking Information in MySQL 5.7 Reference Manual.

Identifying Blocking Transactions

It is sometimes helpful to identify which transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. (For descriptions of these tables, see Section 15.14.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 the sys schema innodb_lock_waits view:

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;
waiting trx idwaiting threadwaiting queryblocking trx idblocking threadblocking query
A46SELECT b FROM t FOR UPDATEA35SELECT SLEEP(100)
A57SELECT c FROM t FOR UPDATEA35SELECT SLEEP(100)
A57SELECT c FROM t FOR UPDATEA46SELECT b FROM t FOR UPDATE

In the preceding table, you can identify sessions by the waiting query or blocking query columns. As you can see:

  • Session B (trx id A4, thread 6) and Session C (trx id A5, thread 7) are both waiting for Session A (trx id A3, thread 5).

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

You can see the underlying data in the INFORMATION_SCHEMA INNODB_TRX table and Performance Schema data_locks and data_lock_waits tables.

The following table shows some sample contents of the INNODB_TRX table.

trx idtrx statetrx startedtrx requested lock idtrx wait startedtrx weighttrx mysql thread idtrx query
A3RUN­NING2008-01-15 16:44:54NULLNULL25SELECT SLEEP(100)
A4LOCK WAIT2008-01-15 16:45:09A4:1:3:22008-01-15 16:45:0926SELECT b FROM t FOR UPDATE
A5LOCK WAIT2008-01-15 16:45:14A5:1:3:22008-01-15 16:45:1427SELECT c FROM t FOR UPDATE

The following table shows some sample contents of the data_locks table.

lock idlock trx idlock modelock typelock schemalock tablelock indexlock data
A3:1:3:2A3XRECORDtesttPRIMARY0x0200
A4:1:3:2A4XRECORDtesttPRIMARY0x0200
A5:1:3:2A5XRECORDtesttPRIMARY0x0200

The following table shows some sample contents of the data_lock_waits table.

requesting trx idrequested lock idblocking trx idblocking lock id
A4A4:1:3:2A3A3:1:3:2
A5A5:1:3:2A3A3:1:3:2
A5A5:1:3:2A4A4:1:3:2
Correlating InnoDB Transactions with MySQL Sessions

Sometimes it is useful to correlate internal InnoDB locking information with the session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB transaction 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 the INFORMATION_SCHEMA INNODB_TRX table and Performance Schema data_locks and data_lock_waits tables is taken from a somewhat loaded system. As can be seen, there are several transactions running.

The following data_locks and data_lock_waits tables show that:

  • Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D, and 77B to commit.

  • Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.

  • Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.

  • Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.

  • Transaction 77A is running, currently executing SELECT.

  • Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.

  • Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.

  • Transaction 19C is running, currently executing an INSERT.

Note

There may be inconsistencies between queries shown in the INFORMATION_SCHEMA PROCESSLIST and INNODB_TRX tables. For an explanation, see Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.

The following table shows the contents of the PROCESSLIST table for a system running a heavy workload.

IDUSERHOSTDBCOMMANDTIMESTATEINFO
384rootlocalhosttestQuery10updateINSERT INTO t2 VALUES …
257rootlocalhosttestQuery3updateINSERT INTO t2 VALUES …
130rootlocalhosttestQuery0updateINSERT INTO t2 VALUES …
61rootlocalhosttestQuery1updateINSERT INTO t2 VALUES …
8rootlocalhosttestQuery1updateINSERT INTO t2 VALUES …
4rootlocalhosttestQuery0preparingSELECT * FROM PROCESSLIST
2rootlocalhosttestSleep566NULL

The following table shows the contents of the INNODB_TRX table for a system running a heavy workload.

trx idtrx statetrx startedtrx requested lock idtrx wait startedtrx weighttrx mysql thread idtrx query
77FLOCK WAIT2008-01-15 13:10:1677F2008-01-15 13:10:161876INSERT INTO t09 (D, B, C) VALUES …
77ELOCK WAIT2008-01-15 13:10:1677E2008-01-15 13:10:161875INSERT INTO t09 (D, B, C) VALUES …
77DLOCK WAIT2008-01-15 13:10:1677D2008-01-15 13:10:161874INSERT INTO t09 (D, B, C) VALUES …
77BLOCK WAIT2008-01-15 13:10:1677B:733:12:12008-01-15 13:10:164873INSERT INTO t09 (D, B, C) VALUES …
77ARUN­NING2008-01-15 13:10:16NULLNULL4872SELECT b, c FROM t09 WHERE …
E56LOCK WAIT2008-01-15 13:10:06E56:743:6:22008-01-15 13:10:065384INSERT INTO t2 VALUES …
E55LOCK WAIT2008-01-15 13:10:06E55:743:38:22008-01-15 13:10:13965257INSERT INTO t2 VALUES …
19CRUN­NING2008-01-15 13:09:10NULLNULL2900130INSERT INTO t2 VALUES …
E15RUN­NING2008-01-15 13:08:59NULLNULL539561INSERT INTO t2 VALUES …
51DRUN­NING2008-01-15 13:08:47NULLNULL98078INSERT INTO t2 VALUES …

The following table shows the contents of the data_lock_waits table for a system running a heavy workload.

requesting trx idrequested lock idblocking trx idblocking lock id
77F77F:80677E77E:806
77F77F:80677D77D:806
77F77F:80677B77B:806
77E77E:80677D77D:806
77E77E:80677B77B:806
77D77D:80677B77B:806
77B77B:733:12:177A77A:733:12:1
E56E56:743:6:2E55E55:743:6:2
E55E55:743:38:219C19C:743:38:2

The following table shows the contents of the data_locks table for a system running a heavy workload.

lock idlock trx idlock modelock typelock schemalock tablelock indexlock data
77F:80677FAUTO_INCTABLEtestt09NULLNULL
77E:80677EAUTO_INCTABLEtestt09NULLNULL
77D:80677DAUTO_INCTABLEtestt09NULLNULL
77B:80677BAUTO_INCTABLEtestt09NULLNULL
77B:733:12:177BXRECORDtestt09PRIMARYsupremum pseudo-record
77A:733:12:177AXRECORDtestt09PRIMARYsupremum pseudo-record
E56:743:6:2E56SRECORDtestt2PRIMARY0, 0
E55:743:6:2E55XRECORDtestt2PRIMARY0, 0
E55:743:38:2E55SRECORDtestt2PRIMARY1922, 1922
19C:743:38:219CXRECORDtestt2PRIMARY1922, 1922

User Comments
Sign Up Login You must be logged in to post a comment.