Mysql lock and lock information view

There are several types of locks in mysql:

Shared lock / exclusive lock

Intentional lock, which is table-level

Record lock

Gap lock

Next-Key lock

Self-increasing lock

Predicate lock for the geographic index.

There are several system tables in mysql about lock information:

INFORMATION_SCHEMA.INNODB_TRX: records all transaction information executed on the INNODB engine.

INFORMATION_SCHEMA.INNODB_LOCKS: records information about locks on each transaction.

INFORMATION_SCHEMA.INNODB_LOCK_WAITS: records which transactions are waiting for a lock, as well as information about waiting for a lock.

Check to see if the lock exists and related information:

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

It should be noted that the INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS tables are recorded from a piece of intermediate memory in innodb, and it is normal to look up the results of these three tables in 0.1s.

However, if you look at the three tables separately for more than 0.1 seconds, the data of the three tables will be inconsistent.

In addition, the data sources of these tables are not consistent with those of the PROCESSLIST table.