mysql死锁处理
访问量:20
创建时间:2026-05-30
通过select * from information_schema.innodb_trx;
trx_state是事务状态。
查看会有很多Lock WAIT 和running的事务信息。Lock WAIT是等待锁，无法获得锁的事务。
还有RUNNing的事务，这是事务运行了但是未提交。导致锁一直占用，其他事务无法获得锁。
根据上面的trx_mysql_thread_id 查看mysql线程
show processlist; KILL <线程ID>;
然后再执行select * from information_schema.innodb_trx; 就没有卡住的事务了。
模拟锁等待现象
创建数据库，和表，并插入数据
[root@localhost ~]# mysql -uroot -p
Enter password:
####创建测试数据库abc
mysql> create database abc;
Query OK, 1 row affected (0.08 sec)
mysql> use abc
Database changed
###创建表user_info
mysql> CREATE TABLE user_info (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> age INT DEFAULT 0,
-> PRIMARY KEY (id),
-> UNIQUE KEY uk_name (name) -- 添加唯一索引有助于更精准地触发行级锁
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.21 sec)
###插入数据。
mysql> INSERT INTO user_info (name, age) VALUES ('session A update', 10);
Query OK, 1 row affected (0.00 sec)
准备3个终端，2个终端执行事务语句，一个终端查看事务
在客户端 A 中开启事务并执行更新操作（持有排他锁）：
-- 关闭自动提交（非必须，但推荐显式声明）
SET autocommit = 0;
-- 开启事务
BEGIN;
-- 对特定行数据执行更新，此时该行会被加上排他锁（X锁）且未释放
UPDATE user_info SET age = 11 WHERE name = 'session A update';
在客户端 B 中尝试更新同一行数据（进入锁等待状态）:
-- 同样开启事务
BEGIN;
-- 为了便于观察超时现象，可以将当前会话的锁等待超时时间设置短一些（例如5秒）
SET innodb_lock_wait_timeout = 5;
-- 尝试更新与客户端A相同的数据行
UPDATE user_info SET age = 12 WHERE name = 'session A update';
在客户端C查看事务状态：
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 44895
trx_state: LOCK WAIT
trx_started: 2026-05-27 15:52:35
trx_requested_lock_id: 139934476967936:19708:126:5:2:139934396638032
trx_wait_started: 2026-05-27 16:47:26
trx_weight: 2
trx_mysql_thread_id: 332
trx_query: UPDATE user_info SET age = 12 WHERE name = 'session A update'
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1128
trx_rows_locked: 3
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
trx_schedule_weight: 1
*************************** 2. row ***************************
trx_id: 44892
trx_state: RUNNING
trx_started: 2026-05-27 15:52:05
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 331
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1128
trx_rows_locked: 2
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: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
2 rows in set (0.00 sec)
可以看到trx_id: 44892 这个锁住了，其他事务处于lock wait状态。
查看 trx_mysql_thread_id: 331 线程之前执行了什么sql.
需要数据库开启performance_schema ，select @@performance_schema结果1为开启。 需要performance_schema.events_statements_history 为YES状态。
mysql> select @@performance_schema;
+----------------------+
| @@performance_schema |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_cpu | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
16 rows in set (0.08 sec)
执行sql查看线程331执行了什么sql.
SELECT
THREAD_ID,
EVENT_ID,
END_EVENT_ID,
SQL_TEXT,
TIMER_START,
TIMER_END
FROM performance_schema.events_statements_history
WHERE THREAD_ID = (
SELECT THREAD_ID
FROM performance_schema.threads
WHERE PROCESSLIST_ID = 8111962 -- 替换为你的 trx_mysql_thread_id
)
ORDER BY TIMER_START DESC
LIMIT 10;
mysql> SELECT
-> THREAD_ID,
-> EVENT_ID,
-> END_EVENT_ID,
-> SQL_TEXT,
-> TIMER_START,
-> TIMER_END
-> FROM performance_schema.events_statements_history
-> WHERE THREAD_ID = (
-> SELECT THREAD_ID
-> FROM performance_schema.threads
-> WHERE PROCESSLIST_ID = 331 -- 替换为你的 trx_mysql_thread_id
-> )
-> ORDER BY TIMER_START DESC
-> LIMIT 10;
+-----------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | SQL_TEXT | TIMER_START | TIMER_END |
+-----------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------------------+
| 365 | 20 | 20 | UPDATE user_info SET age = 11 WHERE name = 'session A update' | 23149048487121000 | 23149048824544000 |
| 365 | 18 | 19 | BEGIN | 23144268179177000 | 23144268252047000 |
| 365 | 17 | 17 | SET autocommit = 0 | 23139540868460000 | 23139540990028000 |
| 365 | 15 | 16 | select * from user_info | 23114404266790000 | 23114404476113000 |
| 365 | 13 | 14 | INSERT INTO user_info (name, age) VALUES ('session A update', 10) | 23103253449346000 | 23103255247807000 |
| 365 | 11 | 12 | CREATE TABLE user_info (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uk_name (name) -- 添加唯一索引有助于更精准地触发行级锁
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | 23076054732122000 | 23076266858470000 |
| 365 | 9 | 10 | show tables | 23072326971074000 | 23072343646553000 |
| 365 | 7 | 8 | show databases | 23072264281185000 | 23072326698521000 |
| 365 | 6 | 6 | NULL | 23072262566689000 | 23072262831024000 |
| 365 | 5 | 5 | SELECT DATABASE() | 23072262125054000 | 23072262327987000 |
+-----------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------------------+
10 rows in set (0.05 sec)
登陆评论:
使用GITHUB登陆