mysql 查看谁在持有锁

如果系统出现死锁，一种方式是通过show engine innodb status查看，但是它只显示谁在等待锁，无法查看到谁持有锁；很多情况下其并不能显示所有锁状态信息；

今天讨论另一个方法，通过information_schema 下的视图来解决；

第一步，认为制造阻塞事物，创建一个表，开启2个会话，将auto_commit设置为OFF；

mysql> select * from test.test;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| a    |
| b    |
| c    |
| d    |
+------+
8 rows in set (0.00 sec)

第一个会话delete from test where id = '4'，不提交；

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id = '1';
Query OK, 1 row affected (0.00 sec)

mysql>

第二个会话执行delete from test where id = 'c';此会话将会阻塞：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where id = 'c';

如果用show engine innodb status可以获取到如下信息，但是只知道该操作被阻塞了，却无法知道谁阻塞的；

MySQL thread id 103817159, OS thread handle 0x7fe5b9220700, query id 8122828304 localhost root updating
delete from test where id = 'c'
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42222 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`test` trx id 3770680359 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

我们用另一个方法：打开第三个会话在infomation_schema库下： select * from innodb_locks;：

mysql> select * from innodb_locks;
+----------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
| lock_id              | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+----------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
| 3770594455:42222:3:2 | 3770594455  | X         | RECORD    | `test`.`test` | GEN_CLUST_INDEX |      42222 |         3 |        2 | 0x000035E92F9F |
| 3770592593:42222:3:2 | 3770592593  | X         | RECORD    | `test`.`test` | GEN_CLUST_INDEX |      42222 |         3 |        2 | 0x000035E92F9F |
+----------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
2 rows in set (0.00 sec)

可以看到2个锁争用，锁模式都是排他锁；可以看到对应2个锁的锁ID及其对应的表；

那么这2个锁，是谁阻塞谁呢？ select * from innodb_lock_waits;这个表可以看到事物ID为3770594455的处于等待状态；所以3770592593先取得的排他锁，未释放，3770594455整等待对方释放；那么他们事物ID知道了，如何知道对应的session id?

mysql> select * from innodb_lock_waits;
+-------------------+----------------------+-----------------+----------------------+
| requesting_trx_id | requested_lock_id    | blocking_trx_id | blocking_lock_id     |
+-------------------+----------------------+-----------------+----------------------+
| 3770594455        | 3770594455:42222:3:2 | 3770592593      | 3770592593:42222:3:2 |
+-------------------+----------------------+-----------------+----------------------+
1 row in set (0.00 sec)

 select * from innodb_trx\G可以查看

trx_id: 3770594455处于 trx_state: LOCK WAIT；trx_id: 3770592593处于trx_state: RUNNING

trx_mysql_thread_id是2个会话分别对应的process id;

trx_query显示在执行的命令

trx_started，trx_wait_started事物开始时间和等待时间

mysql> select * from innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 3770594455
                 trx_state: LOCK WAIT
               trx_started: 2019-02-18 11:37:23
     trx_requested_lock_id: 3770594455:42222:3:2
          trx_wait_started: 2019-02-18 11:44:47
                trx_weight: 2
       trx_mysql_thread_id: 103817159
                 trx_query: delete from test where id = '4'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1184
           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: 3770592593
                 trx_state: RUNNING
               trx_started: 2019-02-18 11:37:02
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 103813050
                 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: 9
         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
2 rows in set (0.00 sec)

mysql> select * from processlist where user='root';
+-----------+------+-----------+--------------------+---------+------+-----------+---------------------------------------------+
| ID        | USER | HOST      | DB                 | COMMAND | TIME | STATE     | INFO                                        |
+-----------+------+-----------+--------------------+---------+------+-----------+---------------------------------------------+
| 103817159 | root | localhost | test               | Query   |   13 | updating  | delete from test where id = '4'             |
| 103813050 | root | localhost | test               | Sleep   |  661 |           | NULL                                        |
| 103818159 | root | localhost | information_schema | Query   |    0 | executing | select * from processlist where user='root' |
+-----------+------+-----------+--------------------+---------+------+-----------+---------------------------------------------+

那么知道是哪个会话产生阻塞，就可以决定是等待 完成，还是kill某一个会话了；