mysql查看锁表情况

43221321

                      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 id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
然后什么也不动，去session2：
session2：
mysql> update a set name='z' where id=1;
会一直等待
session3：
mysql> show full  processlist ;
+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+
| Id      | User | Host                | db                 | Command | Time   | State    | Info                             |
+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+                   |
| 1720902 | root | localhost           | log                | Sleep   |    977 |          | NULL                             |
| 1720906 | root | localhost           | log                | Query   |    294 | Updating | update a set name='z' where id=1 |
| 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 id=1
       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 size 376, 7 row lock(s), undo log entries 1
MySQL thread id 1720902, OS thread handle 0x7fc7c6e76700, query id 61920628 localhost root
--------
FILE I/O
--------
EB9B458这个事物号在等待1720902 这个pid结束进程。