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

Editor to share with you MySQL how to view the current database lock request, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL version: 5.6.21

MySQL provides three ways to view the current database lock request:

1. Show full processlist command

Observe the state and info columns

2. Show engine innodb status\ G command

View the TRANSACTIONS section and the LATEST DETECTED DEADLOCK section

3. Three tables under information_shcema (through which you can update and monitor current things and analyze lock problems)

-- innodb_trx

Print current active (ACTIVE) transactions in the innodb kernel)

-- innodb_locks

(the innodb lock resulting from printing the current state is printed only when there is a lock waiting)

-- innodb_lock_waits

(print the innodb lock waiting generated by the current state to print only when there is a lock waiting)

1) description of the structure of innodb_ trx table (extract the 8 fields that are most illustrative)

The field name describes the unique thing within the trx_idinnodb storage engine IDtrx_state

Current state of things (running and lock wait) trx_started

The start time of the thing trx_requested_lock_id waits for the lock ID of the thing, for example, the state of trx_state is Lock wait, then this value takes the ID of the resource occupied by the thing before the current thing waits. If trx_state is not Lock wait, then this value is the weight of the trx_weight thing that the NULLtrx_wait_started transaction waits. In the innodb storage engine, when a deadlock occurs and needs to be rolled back The innodb storage engine will select the one with the lowest value to roll back the thread id in TRX _ mysql_thread_idmysql, that is, the SQL statement that the trx_query transaction runs as the result displayed by show processlist.

2) innodb_locks table structure description

Field name description lock_id

IDlock_trx_id of the lock

IDlock_mode of things

Lock mode (S lock and X lock mode) lock_type

Type of lock table lock or row lock (RECORD) lock_table

Table to be locked lock_index

Locked index lock_space

Lock the space idlock_page of an object

The number of pages locked by the transaction, or NULLlock_rec if the table is locked

The number of rows locked by the transaction, or NULLlock_data if the table is locked

The transaction locks the record primary key value, which is NULL if it is a table lock (this option cannot be trusted)

3) description of innodb_lock_ waits table structure

Field name description requesting_trx_id

Apply for locking resource IDrequested_lock_id

IDblocking_trx_id of the lock applied for

Things that block other things IDblocking_lock_id

Lock ID that blocks other locks

We can make a joint query according to these three tables to get more intuitive and clear results. You can refer to the following SQL (you can adjust it according to your analysis habits) selectr.trx_isolation_level,r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread,r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type,lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query B.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state,lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table,lb.lock_index blocking_trx_lock_index B.trx_query blocking_queryfrom information_schema.innodb_lock_waits w inner join information_schema.innodb_trx bon b.trx_id=w.blocking_trx_id inner join information_schema.innodb_trx ron r.trx_id=w.requesting_trx_id inner join information_schema.innodb_locks lbon lb.lock_trx_id=w.blocking_trx_idinner join information_schema.innodb_locks lron lr.lock_trx_id=w.requesting_trx_id\ G * * 1. Row * * trx_isolation_level: REPEATABLE READ waiting_trx_id: 2900247 waiting_trx_thread: 1070 waiting_trx_state: LOCK WAIT waiting_trx_lock_mode: s waiting_trx_lock_type: RECORD waiting_trx_lock _ table: `jiang_ test`.`test` waiting_trx_lock_index: PRIMARY waiting_trx_query: select * from test where id=3 lockin share mode blocking_trx_id: 2900241 blocking_trx_thread: 1137 blocking_trx_state: RUNNING blocking_trx_lock_mode: X blocking_trx_lock_type: RECORDblocking_trx_lock_table: `jiang_ test`.`test`thanks _ trx_lock_index: PRIMARY blocking_query: NULL1 row in set (0.01 sec)

The above is all the contents of the article "how to view current database lock requests by MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.