mysql查看锁表情况
root@leyou:~# mysql -uroot -pxxx logmysql> 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: 1Changed: 1Warnings: 0
然后什么也不动，去session2：
session2：
mysql> update a set name='z' where id=1;
会一直等待
session3：
mysql> show fullprocesslist ;
+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+
| 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 fullprocesslist |
+---------+------+---------------------+--------------------+---------+--------+----------+----------------------------------+
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: EB9B458pid为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结束进程。
页:
[1]