INNODB_LOCKS Table: INNODB_LOCKS 表 包含信息关于每个锁 一个InnoDB 事务已经请求 但是没有获得锁,
每个lock 一个事务持有是堵塞另外一个事务
centos6.5:/root/mysql-5.6.22#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRXG "
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
trx_id: 5460
trx_state: LOCK WAIT
trx_started: 2016-11-22 15:02:18
trx_requested_lock_id: 5460:14:3:2
trx_wait_started: 2016-11-22 15:02:18
trx_weight: 2
trx_mysql_thread_id: 1404
trx_query: delete from test where username='admin'
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 0
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
*************************** 2. row ***************************
trx_id: 5453
trx_state: RUNNING
trx_started: 2016-11-22 14:01:14
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1367
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 4
trx_rows_modified: 1
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
centos6.5:/root/mysql-5.6.22#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_LOCKSG "
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
lock_id: 5460:14:3:2
lock_trx_id: 5460
lock_mode: X
lock_type: RECORD
lock_table: `DEVOPS`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 14
lock_page: 3
lock_rec: 2
lock_data: 0x000000000218
*************************** 2. row ***************************
lock_id: 5453:14:3:2
lock_trx_id: 5453
lock_mode: X
lock_type: RECORD
lock_table: `DEVOPS`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 14
lock_page: 3
lock_rec: 2
lock_data: 0x000000000218
centos6.5:/root/mysql-5.6.22#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist"
Warning: Using a password on the command line interface can be insecure.
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
| 1367 | root | 192.168.11.186:40366 | DEVOPS | Sleep | 3905 | | NULL |
| 1404 | root | 192.168.11.186:46149 | DEVOPS | Query | 4 | updating | delete from test where username='admin' |
| 1413 | root | 10.10.11.191:54394 | NULL | Sleep | 519 | | NULL |
| 1414 | root | 10.10.11.191:54395 | NULL | Sleep | 519 | | NULL |
| 1461 | root | 192.168.11.185:49157 | NULL | Query | 0 | init | show processlist |
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
INNODB_LOCKS Columns:
LOCK_ID 唯一的lock ID 号,内部与InnoDB.
对待它作为一个不透明的字符串。
虽然 LOCK_ID 当前包含TRX_ID
LOCK_TRX_ID 事务持有锁的的ID 得到关于事务的详细信息,关联这个列和INNODB_TRX 表的TRX_ID