MySQL 5.5 – innodb_lock_wait 锁 等待
在5.5中,information_schema 库中增加了三个关于锁的表(MEMORY引擎)
- innodb_trx ## 当前运行的所有事务
- innodb_locks ## 当前出现的锁
- nnodb_lock_waits ## 锁等待的对应关系
mysql> select * from information_schema.innodb_trx \G;
*************************** 1. row ***************************
trx_id: 466712
trx_state: RUNNING
trx_started: 2016-08-11 16:25:00
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 4295
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 3
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "fr_f_disconf";
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
| 4267 | root | localhost | fr_f_disconf | Query | 0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "fr_f_disconf" |
| 4288 | root | VM_200_212_centos:42437 | fr_f_disconf | Sleep | 872 | | NULL |
| 4291 | root | VM_200_212_centos:42440 | fr_f_disconf | Sleep | 872 | | NULL |
| 4293 | root | VM_200_212_centos:42442 | fr_f_disconf | Sleep | 872 | | NULL |
| 4290 | root | VM_200_212_centos:42439 | fr_f_disconf | Sleep | 222 | | NULL |
| 4292 | root | VM_200_212_centos:42441 | fr_f_disconf | Sleep | 872 | | NULL |
| 4294 | root | VM_200_212_centos:42443 | fr_f_disconf | Sleep | 872 | | NULL |
| 4295 | root | VM_200_212_centos:42444 | fr_f_disconf | Sleep | 72 | | NULL |
| 4289 | root | VM_200_212_centos:42438 | fr_f_disconf | Sleep | 872 | | NULL |
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS \G;
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
mysql> show variables like "%timeout%";
mysql> show processlist;