MySQL锁情况查看命令

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.38-log |
+------------+
1 row in set (0.01 sec)

mysql> show create table t\G;
*************************** 1. row ***************************
   Table: t
Create Table: CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | a  |
| 2 | s  |
| 3 | c  |
| 4 | d  |
| 5 | e  |
+----+------+
5 rows in set (0.00 sec)

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

mysql> select * from t where id<5 for update;
+----+------+
| id | name |
+----+------+
| 1 | a  |
| 2 | s  |
| 3 | c  |
| 4 | d  |
+----+------+
4 rows in set (0.00 sec)

mysql> insert into t values(0,'null');

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from innodb_trx\G;
*************************** 1. row ***************************
          trx_id: 8553
        trx_state: LOCK WAIT
       trx_started: 2022-12-14 16:52:29
  trx_requested_lock_id: 8553:45:3:2
     trx_wait_started: 2022-12-14 16:52:29
        trx_weight: 2
   trx_mysql_thread_id: 22
        trx_query: insert into t values(0,'null')
   trx_operation_state: inserting
    trx_tables_in_use: 1
    trx_tables_locked: 1
     trx_lock_structs: 2
  trx_lock_memory_bytes: 1136
     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: 0
     trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
          trx_id: 8552
        trx_state: RUNNING
       trx_started: 2022-12-14 16:51:39
  trx_requested_lock_id: NULL
     trx_wait_started: NULL
        trx_weight: 2
   trx_mysql_thread_id: 20
        trx_query: NULL
   trx_operation_state: NULL
    trx_tables_in_use: 0
    trx_tables_locked: 1
     trx_lock_structs: 2
  trx_lock_memory_bytes: 1136
     trx_rows_locked: 5
    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: 0
     trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> show full processlist;
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| Id | User  | Host   | db         | Command | Time | State  | Info              |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
| 20 | root  | localhost | ray        | Sleep  | 132 |     | NULL              |
| 22 | raybak | localhost | ray        | Query  |  82 | update  | insert into t values(0,'null') |
| 24 | root  | localhost | information_schema | Query  |  0 | starting | show full processlist     |
+----+--------+-----------+--------------------+---------+------+----------+--------------------------------+
3 rows in set (0.00 sec)

mysql> select * from innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 8553
requested_lock_id: 8553:45:3:2
  blocking_trx_id: 8552
 blocking_lock_id: 8552:45:3:2
1 row in set, 1 warning (0.00 sec)

mysql> select * from innodb_locks\G;
*************************** 1. row ***************************
  lock_id: 8553:45:3:2
lock_trx_id: 8553
 lock_mode: X,GAP
 lock_type: RECORD
lock_table: `ray`.`t`
lock_index: PRIMARY
lock_space: 45
 lock_page: 3
 lock_rec: 2
 lock_data: 1
*************************** 2. row ***************************
  lock_id: 8552:45:3:2
lock_trx_id: 8552
 lock_mode: X
 lock_type: RECORD
lock_table: `ray`.`t`
lock_index: PRIMARY
lock_space: 45
 lock_page: 3
 lock_rec: 2
 lock_data: 1
2 rows in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql> kill 20;
Query OK, 0 rows affected (0.00 sec)