【mysql】1205 -Lock wait timeout exceeded； try restarting transaction

问题：

mysql8执行SQL提示下面错误：

1205 -Lock wait timeout exceeded; try restarting transaction

1205-超过锁定等待超时；尝试重新启动事务

可能的原因：

1. 事务冲突：多个事务同时尝试修改同一行数据，导致锁等待。

2. 长时间运行的事务：某个事务长时间持有锁，导致其他事务无法获取锁。

3. 死锁：两个或多个事务相互等待对方持有的锁，形成死锁。

4. 锁等待超时设置过短：MySQL 的锁等待超时时间设置过短，导致事务在等待锁时超时。

问题解决

SHOW ENGINE INNODB STATUS

完整信息：

=====================================
2024-12-20 15:49:38 140453673359040 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 33175 srv_active, 0 srv_shutdown, 925766 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 44544
OS WAIT ARRAY INFO: signal count 42938
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 660638
Purge done for trx's n:o < 657904 undo n:o < 0 state: running but idle
History list length 272
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421929904271080, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904250880, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904274312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904251688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904275928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904273504, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904275120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904272696, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904271888, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904250072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904270272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904269464, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904268656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904267848, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904266232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904265424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904264616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904261384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904260576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904255728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904254112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904259768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904258960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904258152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904257344, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904256536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904254920, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904253304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904252496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904263808, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904263000, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904262192, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904249264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421929904248456, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 657894, ACTIVE 3939 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 3
MySQL thread id 108531, OS thread handle 140453676521152, query id 2979843 _gateway 192.168.22.1 root
Trx read view will not see trx with id >= 657889, sees < 657889
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (read thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (write thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
2288 OS file reads, 1251819 OS file writes, 1355411 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.90 writes/s, 2.04 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:insert 0, delete mark 0, delete 0
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 9 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
0.48 hash searches/s, 1.03 non-hash searches/s
---
LOG
---
Log sequence number          218843926
Log buffer assigned up to    218843926
Log buffer completed up to   218843926
Log written up to            218843926
Log flushed up to            218843926
Added dirty pages up to      218843926
Pages flushed up to          218843926
Last checkpoint at           218843926
Log minimum file id is       58
Log maximum file id is       66
362857 log i/o's done, 0.60 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 2344316
Buffer pool size   8192
Free buffers       5861
Database pages     2308
Old database pages 831
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1281, not young 1804
0.03 youngs/s, 0.00 non-youngs/s
Pages read 1415, created 894, written 411439
0.00 reads/s, 0.00 creates/s, 0.63 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 6 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2308, unzip_LRU len: 0
I/O sum[32]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=6829, Main thread ID=140454087620288 , state=sleeping
Number of rows inserted 9714, updated 33890, deleted 337, read 1209627
0.00 inserts/s, 0.07 updates/s, 0.00 deletes/s, 0.90 reads/s
Number of system rows inserted 6941, updated 4455, deleted 3239, read 117481
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

事务状态

• 活跃事务:

  • 有一个事务（Trx id counter 660638）处于 ACTIVE 3939 sec 状态，表示该事务已经运行了 3939 秒（超过 1 小时）。

  • 这个长时间运行的事务可能会持有锁，导致其他事务等待锁超时。

• 其他事务:

  • 其他事务都处于 not started 状态，表示它们尚未开始执行。

锁信息

• 长时间运行的事务持有 1 行锁（1 row lock(s)），这可能导致其他事务在等待锁时超时。

I/O 状态

• I/O 线程状态:

  • 所有 I/O 线程都处于 waiting for completed aio requests 状态，表示 I/O 操作正常，没有明显的瓶颈。

• Pending I/O:

  • 没有挂起的 I/O 操作（Pending normal aio reads: [0, 0, 0, 0]），说明 I/O 操作没有阻塞。

Buffer Pool 状态

• Buffer Pool 命中率:

  • 缓冲池命中率非常高（Buffer pool hit rate 1000 / 1000），表示内存缓存有效，查询性能没有受到内存瓶颈的影响。

• Modified Pages:

  • 没有脏页（Modified db pages 0），表示没有未刷新的数据页。

终止长时间运行的事务

• 使用以下命令终止长时间运行的事务：

  KILL <thread_id>;

• 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 3
  MySQL thread id 108531, OS thread handle 140453676521152, query id 2979843 _gateway 192.168.22.1 root
  Trx read view will not see trx with id >= 657889, sees < 657889
  

• 从输出中可以看到，长时间运行的事务对应的线程 ID 是 108531，因此可以执行：

  KILL 108531;

问题解决了

---

---

---