13.7.6.2. Information Schema Tables about Transactions

Three InnoDB-related Information Schema tables make it easy to monitor transactions and diagnose possible locking problems. The three tables are INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS.

13.7.6.2.1. INNODB_TRX

Contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the particular SQL statement the transaction is executing.

For the table definition, see Table 20.3, “INNODB_TRX Columns”.

13.7.6.2.2. INNODB_LOCKS

Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE='LOCK WAIT') is blocked by exactly one “blocking lock request”. That blocking lock request is for a row or table lock held by another transaction in an incompatible mode. The waiting or blocked transaction cannot proceed until the other transaction commits or rolls back, thereby releasing the requested lock. For every blocked transaction, INNODB_LOCKS contains one row that describes each lock the transaction has requested, and for which it is waiting. INNODB_LOCKS also contains one row for each lock that is blocking another transaction, whatever the state of the transaction that holds the lock ('RUNNING', 'LOCK WAIT', 'ROLLING BACK' or 'COMMITTING'). The lock that is blocking a transaction is always held in a mode (read vs. write, shared vs. exclusive) incompatible with the mode of requested lock.

For the table definition, see Table 20.4, “INNODB_LOCKS Columns”.

13.7.6.2.3. INNODB_LOCK_WAITS

Using this table, you can tell which transactions are waiting for a given lock, or for which lock a given transaction is waiting. This table contains one or more rows for each blocked transaction, indicating the lock it has requested and the lock(s) that is (are) blocking that request. The REQUESTED_LOCK_ID refers to the lock that a transaction is requesting, and the BLOCKING_LOCK_ID refers to the lock (held by another transaction) that is preventing the first transaction from proceeding. For any given blocked transaction, all rows in INNODB_LOCK_WAITS have the same value for REQUESTED_LOCK_ID and different values for BLOCKING_LOCK_ID.

For the table definition, see Table 20.5, “INNODB_LOCK_WAITS Columns”.

13.7.6.2.4. Using the Transaction Information Schema Tables

Example 13.2. Identifying Blocking Transactions

It is sometimes helpful to be able to identify which transaction is blocking another. You can use the Information Schema tables to find out which transaction is waiting for another, and which resource is being requested.

Suppose you have the following scenario, with three users running concurrently. Each user (or session) corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these users have issued the following commands, but none has yet committed its transaction:

  • User A:

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

    SELECT b FROM t FOR UPDATE;
    
  • User C:

    SELECT c FROM t FOR UPDATE;
    

In this scenario, you may use this query to see who is waiting for whom:

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 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 above result, you can identify users by the “waiting query” or “blocking query”. As you can see:

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

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

You can see the underlying data in the tables INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS.

The following table shows some sample Contents of INFORMATION_SCHEMA.INNODB_TRX.

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 INFORMATION_SCHEMA.INNODB_LOCKS.

lock idlock trx idlock modelock typelock tablelock indexlock spacelock pagelock reclock data
A3:1:3:2A3XRECORD`test`.`t``PRIMARY`1320x0200
A4:1:3:2A4XRECORD`test`.`t``PRIMARY`1320x0200
A5:1:3:2A5XRECORD`test`.`t``PRIMARY`1320x0200

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS.

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

Example 13.3. More Complex Example of Transaction Data in Information Schema Tables

Sometimes you would like to correlate the internal InnoDB locking information with 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 user that may be holding a lock, and thus blocking another transaction.

The following output from the INFORMATION_SCHEMA tables is taken from a somewhat loaded system.

As can be seen in the following tables, there are several transactions running.

The following INNODB_LOCKS and INNODB_LOCK_WAITS tables shows 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 that there may be an inconsistency between queries shown in the two tables INNODB_TRX.TRX_QUERY and PROCESSLIST.INFO. The current transaction ID for a thread, and the query being executed in that transaction, may be different in these two tables for any given thread. See Section 13.7.6.3.3, “Possible Inconsistency with PROCESSLIST for an explanation.

The following table shows the contents of INFORMATION_SCHEMA.PROCESSLIST in a loaded system.

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 INFORMATION_SCHEMA.INNODB_TRX in a loaded system.

trx idtrx statetrx startedtrx requested lock idtrx wait startedtrx weighttrx mysql thread idtrx query
77FLOCK WAIT2008-01-15 13:10:1677F:8062008-01-15 13:10:161876insert into t09 (D, B, C) values …
77ELOCK WAIT2008-01-15 13:10:1677E:8062008-01-15 13:10:161875insert into t09 (D, B, C) values …
77DLOCK WAIT2008-01-15 13:10:1677D:8062008-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 INFORMATION_SCHEMA.INNODB_LOCK_WAITS in a loaded system

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 INFORMATION_SCHEMA.INNODB_LOCKS in a loaded system.

lock idlock trx idlock modelock typelock tablelock indexlock spacelock pagelock reclock data
77F:80677FAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77E:80677EAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77D:80677DAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77B:80677BAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77B:733​:12:177BXRECORD`test`​.`t09``PRIMARY`733121supremum pseudo-record
77A:733​:12:177AXRECORD`test`​.`t09``PRIMARY`733121supremum pseudo-record
E56:743​:6:2E56SRECORD`test`​.`t2``PRIMARY`743620, 0
E55:743​:6:2E55XRECORD`test`​.`t2``PRIMARY`743620, 0
E55:743​:38:2E55SRECORD`test`​.`t2``PRIMARY`7433821922, 1922
19C:743​:38:219CXRECORD`test`​.`t2``PRIMARY`7433821922, 1922
Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout