Using the Transaction Information Schema Tables

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 MariaDB 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 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 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 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 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`

The following table shows some sample contents of 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`

Example 13.4. 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 MariaDB 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 , "Possible Inconsistency with PROCESSLIST" for an explanation.

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

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`

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

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 …`

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

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

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

Using the Transaction Information Schema Tables

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

Retornar