Shulou(Shulou.com)05/31 Report--

This article is about how MySQL 5. 7 queries InnoDB lock tables. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are three tables in InnoDB INFORMATION_SCHEMA that can be used to monitor and diagnose lock problems.

INNODB_TRX

Contains information about each transaction being executed in InnoDB, including whether the transaction is waiting for a lock, the start time of the transaction, and the SQL statement that the transaction is executing.

INNODB_LOCKS

Record information about each transaction in the InnoDB that is waiting for another transaction to release the lock (INNODB_TRX.TRX_STATE='LOCK WAIT'). These transactions are blocked by the "blocking lock request" event, and the request for these locks is a row lock or table lock occupied by another transaction.

A transaction that waits or blocks cannot proceed until the transaction that owns the lock commits or rolls back. This table records the lock requested by the transaction, the transaction information that holds the lock, the state of the transaction that owns the lock ('RUNNING',' LOCK WAIT', 'ROLLING BACK' or' COMMITTING'), and the mode of the lock (read vs. Write, shared vs. Exclusive).

INNODB_LOCK_WAITS

Record which transactions are waiting for locks and the type of locks they are waiting for, and REQUESTED_LOCK_ID represents the ID,BLOCKING_LOCK_ID of the lock requested by the transaction represents the ID that holds the lock.

Transaction 1

Mysql > BEGIN

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT MSISDN FROM T50 FOR UPDATE

+-+

| | MSISDN |

+-+

| | + 3301000000011 |

| | + 3301000000013 |

| | + 3301000000015 |

| | + 3301000000015 |

| | + 3301000000017 |

| | + 3301000000019 |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT SLEEP (1000)

Transaction 2

Mysql > SELECT IMEI FROM T50 FOR UPDATE

Transaction 3

Mysql > SELECT IMSI FROM T50 FOR UPDATE

Open another session to view thread information

Mysql > show processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+-+-

| | 70 | root | localhost | test | Query | 8 | Sending data | SELECT IMEI FROM T50 FOR UPDATE | 0 | 0 |

| | 71 | root | localhost | test | Query | 310 | User sleep | SELECT SLEEP (1000) | 0 | 0 |

| | 72 | root | localhost | test | Query | 6 | Sending data | SELECT IMSI FROM T50 FOR UPDATE | 0 | 0 |

| | 73 | root | localhost | test | Query | 0 | init | show processlist | 0 | 0 |

+-+-

4 rows in set (0.03 sec)

View lock information

Mysql > 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 | |

+- -+

| | 6288648 | 72 | SELECT IMSI FROM T50 FOR UPDATE | 6288647 | 70 | SELECT IMEI FROM T50 FOR UPDATE | |

| | 6288648 | 72 | SELECT IMSI FROM T50 FOR UPDATE | 6288638 | 71 | SELECT SLEEP (1000) |

| | 6288647 | 70 | SELECT IMEI FROM T50 FOR UPDATE | 6288638 | 71 | SELECT SLEEP (1000) |

+- -+

3 rows in set (0.00 sec)

As you can see, the thread that initially executed the SQL is 71, thread 70 is waiting for thread 71, thread 72 is waiting for thread 70,71

Mysql > select * from 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 |

+- -+-+

| | 6288651GRG 783JV 2 | 6288651 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 | |

| | 6288650 0x000000000607 783Ze2 | 6288650 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 |

| | 6288638 0x000000000607 78 / 3 | 6288638 | X | RECORD | `test`.`t50` | GEN_CLUST_INDEX | 78 | 3 | 2 | 0x000000000607 |

+- -+-+

3 rows in set (0.00 sec)

Mysql > select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_mysql_thread_id,trx_query from information_schema.INNODB_TRX

+- -- +

| | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_mysql_thread_id | trx_query | |

+- -- +

| | 6288669 | LOCK WAIT | 2016-09-05 14:14:28 | 6288669-78-05-78-3 FOR UPDATE 2 | 2016-09-05 14:14:28 | 72 | SELECT IMSI FROM T50 FOR UPDATE |

| | 6288668 | LOCK WAIT | 2016-09-05 14:14:26 | 6288668-78-05-3 FOR UPDATE 2 | 2016-09-05 14:14:26 | 70 | SELECT IMEI FROM T50 FOR UPDATE |

| | 6288638 | RUNNING | 2016-09-05 11:41:59 | NULL | NULL | 71 | SELECT SLEEP (1000) |

+- -- +

3 rows in set (0.00 sec)

Mysql > select * from information_schema.INNODB_LOCK_WAITS

+-+

| | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | |

+-+

| | 6288671 | 6288671 | 78771GRV 783V2 | 6288670 | 6288670PUBG 7870PUR 783JUR 2 | |

| | 6288671 | 6288671 | 78771GRV 783V2 | 6288638 | 6288638Rd 78Rd 78WR 2 | |

| | 6288670 | 6288670 78Frev 3Ze2 | 6288638 | 6288638 Rd 78Rd 3Ze2 | |

+-+

3 rows in set (0.00 sec)

Check the Innodb_row_lock state variable to analyze the row lock contention on the system

Mysql > show global status like'% innodb%row%lock%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_row_lock_current_waits | 0 | |

| | Innodb_current_row_locks | 0 | |

| | Innodb_row_lock_time | 0 | |

| | Innodb_row_lock_time_avg | 0 | |

| | Innodb_row_lock_time_max | 0 | |

| | Innodb_row_lock_waits | 0 | |

+-+ +

6 rows in set (0.00 sec)

Thank you for reading! This is the end of the article on "how to query the InnoDB Lock Table in MySQL 5.7". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.