mysql查看锁表情况

恒晖瑶

　　root@leyou:~# mysql -uroot -pxxx log
　　mysql> show variables like '%innodb_lock_wait_timeout%';
　　+--------------------------+-------+
　　| Variable_name            | Value |
　　+--------------------------+-------+
　　| innodb_lock_wait_timeout | 50    |
　　+--------------------------+-------+
　　1 row in set (0.00 sec)
　　mysql>
　　mysql> set global innodb_lock_wait_timeout=300;
　　Query OK, 0 rows affected (0.00 sec)
　　session1：
　　mysql> begin;
　　Query OK, 0 rows affected (0.00 sec)

　　mysql> update a set name='x' where>　　Query OK, 1 row affected (0.02 sec)
　　Rows matched: 1  Changed: 1  Warnings: 0
　　然后什么也不动，去session2：
　　session2：

　　mysql> update a set name='z' where>　　会一直等待
　　session3：
　　mysql> show full  processlist ;
　　+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+

　　|>　　+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+                   |
　　| 1720902 | root | localhost           | log                | Sleep   |    977 |          | NULL                             |

　　| 1720906 | root | localhost           | log                | Query   |    294 | Updating | update a set name='z' where>　　| 1720907 | root | localhost           | information_schema | Query   |      0 | NULL     | show full  processlist           |
　　+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+
　　8 rows in set (0.00 sec)
　　mysql> show OPEN TABLES where In_use > 0;
　　+----------+-------+--------+-------------+
　　| Database | Table | In_use | Name_locked |
　　+----------+-------+--------+-------------+
　　| log      | a     |      1 |           0 |
　　+----------+-------+--------+-------------+
　　1 row in set (0.00 sec)
　　可以看到是log库的表a现在被占用。
　　继续查看：
　　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      |
　　+--------------------+-------------+-----------+-----------+------------+-------------------+------------+-----------+----------+----------------+
　　| EB9B45A:116492:3:2 | EB9B45B     | X         | RECORD    | `log`.`a`  | `GEN_CLUST_INDEX` |     116492 |         3 |        2 | 0x0000B83D5770 |
　　| EB9B458:116492:3:2 | EB9B458     | X         | RECORD    | `log`.`a`  | `GEN_CLUST_INDEX` |     116492 |         3 |        2 | 0x0000B83D5770 |
　　+--------------------+-------------+-----------+-----------+------------+-------------------+------------+-----------+----------+----------------+
　　2 rows in set (0.00 sec)
　　有两个锁，事物号分别是EB9B45B     以及EB9B458  ，锁的模式都是X，及排它锁，都是锁住了表a，的lock_space 116492  第3页。
　　查看正在等待锁的表：
　　mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
　　+-------------------+--------------------+-----------------+--------------------+
　　| requesting_trx_id | requested_lock_id  | blocking_trx_id | blocking_lock_id   |
　　+-------------------+--------------------+-----------------+--------------------+
　　| EB9B45B           | EB9B45B:116492:3:2 | EB9B458         | EB9B458:116492:3:2 |
　　+-------------------+--------------------+-----------------+--------------------+
　　1 row in set (0.00 sec)
　　这个事物号：EB9B45B           还在等待行级排它锁被grant，所以就需要kill掉EB9B458的pid
　　再查看另一个视图：
　　mysql> SELECT * FROM information_schema.INNODB_TRX\G;
　　*************************** 1. row ***************************
　　trx_id: EB9B45B
　　trx_state: LOCK WAIT
　　trx_started: 2016-11-15 10:11:25
　　trx_requested_lock_id: EB9B45B:116492:3:2
　　trx_wait_started: 2016-11-15 10:11:25
　　trx_weight: 2
　　trx_mysql_thread_id: 1720906

　　trx_query: update a set name='z' where>　　trx_operation_state: starting index read
　　trx_tables_in_use: 1
　　trx_tables_locked: 1
　　trx_lock_structs: 2
　　trx_lock_memory_bytes: 376
　　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
　　*************************** 2. row ***************************
　　trx_id: EB9B458
　　trx_state: RUNNING
　　trx_started: 2016-11-15 10:00:02
　　trx_requested_lock_id: NULL
　　trx_wait_started: NULL
　　trx_weight: 3
　　trx_mysql_thread_id: 1720902
　　trx_query: NULL
　　trx_operation_state: NULL
　　trx_tables_in_use: 0
　　trx_tables_locked: 0
　　trx_lock_structs: 2
　　trx_lock_memory_bytes: 376
　　trx_rows_locked: 7
　　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
　　2 rows in set (0.00 sec)
　　可以看到更详细的锁表情况，
　　trx_id: EB9B458  pid为1720902 的，状态 trx_state: RUNNING，kill掉这个pid就释放了排它锁。
　　此外，用mysql> show engine innodb status\G; 也可以看到结束了但是未提交的事物：
　　关键信息如下：
　　在------------
　　TRANSACTIONS
　　------------
　　这一栏查看：
　　------------------
　　---TRANSACTION EB9B458, ACTIVE 1978 sec

　　2 lock struct(s), heap>
　　MySQL thread>　　--------
　　FILE I/O
　　--------
　　EB9B458这个事物号在等待1720902 这个pid结束进程。