mysql如何查看正在运行的事务和事务上加的锁

一：mysql如何查看正在运行的事务

mysql> select * from information_schema.innodb_trx;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 477279 | LOCK WAIT | 2020-11-11 14:03:57 | 477279:977:3:2 | 2020-11-11 14:09:05 | 2 | 5901 | update finance set money=money-10,update_time=12345678 WHERE uid=1 AND update_time=123456789 | starting index read | 1 | 1 | 2 | 1136 | 4 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 |
| 477272 | RUNNING | 2020-11-11 13:45:22 | NULL | NULL | 4 | 5902 | NULL | NULL | 0 | 1 | 2 | 1136 | 5 | 2 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)

二：查看正在锁的事务

查看事务加锁的类型,我们看到这两个事务加的锁都是X锁

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 |
+----------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
| 477279:977:3:2 | 477279 | X | RECORD | `account`.`finance` | PRIMARY | 977 | 3 | 2 | 1 |
| 477272:977:3:2 | 477272 | X | RECORD | `account`.`finance` | PRIMARY | 977 | 3 | 2 | 1 |
+----------------+-------------+-----------+-----------+---------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

上面我们演示的例子是两个mysql 的session id 在事务中同时执行

update finance set money=money-10,update_time=12345678 WHERE uid=1 AND update_time=123456789；

这条SQL，

两条 SQL都没commit;

发现后面运行的session id出现LOCK WAIT，也就是等待前面执行的session id 释放x锁。

三：查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;