1. 查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;
mysql> SELECT * FROM information_schema.INNODB_TRX \G;
*************************** 1. row ***************************
trx_id: 331123705
trx_state: RUNNING
trx_started: 2022-07-19 13:47:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 11355
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 360
trx_rows_locked: 0
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: 331123681
trx_state: RUNNING
trx_started: 2022-07-19 13:47:09
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 397
trx_mysql_thread_id: 11409
trx_query: update sys_role_user ru
inner join sys_role r on ru.client_role_id = r.client_role_id
inner join sys_user_center uc on ru.client_user_id = uc.id
set ru.role_id = r.id, ru.open_id = uc.open_id
where (ru.role_id is null or ru.open_id is null) and ru.update_time >= '2022-07-19 13:47:09'
and ru.client_id = 'webApp' and ru.`enable` = '1000102'
trx_operation_state: fetching rows
trx_tables_in_use: 3
trx_tables_locked: 3
trx_lock_structs: 397
trx_lock_memory_bytes: 63016
trx_rows_locked: 109781
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: 9974
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
如果发现 trx_state
为 LOCK_WAIT
则发生死锁 ,可以用trx_mysql_thread_id
mysql线程来,通过kill 线程
杀死线程
2 . 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3. 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4. 查询是否锁表
SHOW OPEN TABLES where In_use > 0;
5. 解决死锁
解除死锁
如果需要解除死锁,有一种最简单粗暴的方式,那就是找到进程id之后,直接干掉。
查看当前正在进行中的进程
show processlist
// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;
这两个命令找出来的进程id 是同一个。
杀掉进程对应的进程
kill id
验证(kill后再看是否还有锁)
SHOW OPEN TABLES where In_use > 0;