mysql 检查锁_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 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结束进程。