当前位置： 首页 > article >正文

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

article 2024/12/21 19:33:35

问题：

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;
     
 

 

问题解决了
 

 

 

 

 

 


                

        

				
				

					

					http://www.kler.cn/a/445480.html
					
				

                

                  
相关文章：

				  
                        

  • qlib优缺点 
    • 


  • （五）FT2232HL高速调试器之--三步实现STM32的VSCODE在线仿真工程搭建 
    • 


  • EGO Swarm翻译 
    • 


  • 【读书笔记】《论语别裁》学而有何乐 
    • 


  • 内容与资讯API优质清单 
    • 


  • 源码分析之Openlayers中OverviewMap鹰眼控件 
    • 

                      
  • 
                        Hive其三，数据库操作，小技巧设置，加载数据等操作
                      
    • 
                    
                      
  • 
                        白嫖内网穿透之神卓互联Linux安装教程（树莓派）
                      
    • 
                    
                      
  • 
                        第一次面试到第一份offer的经历分享
                      
    • 
                    
                      
  • 
                        勤研低代码平台：重塑软件开发协作新生态
                      
    • 
                    
                      
  • 
                        Mamba安装环境和使用，anaconda环境打包
                      
    • 
                    
                      
  • 
                        SpringBoot 编程式事务使用
                      
    • 
                    
                      
  • 
                        2024最新CF罗技鼠标宏
                      
    • 
                    
                      
  • 
                        门店全域推广，线下商家营销布局的增量新高地
                      
    • 
                    
                      
  • 
                        vue.js框架概述
                      
    • 
                    
                      
  • 
                        29. 多线程编程
                      
    • 
                    
                      
  • 
                        对象的状态变化处理与工厂模式实现
                      
    • 
                    
                      
  • 
                        UI Automator Viewer操作
                      
    • 
                    
                      
  • 
                        ASCII码简介以及在php中的使用
                      
    • 
                    
                      
  • 
                        JavaSE——绘图入门
                      
    • 
                    
                      
  • 
                        HTTP接口报错详解与解决 200,500,403,408,404
                      
    • 
                    
                      
  • 
                        【Pandas】pandas Series shape
                      
    • 
                    
                      
  • 
                        【Cadence射频仿真学习笔记】IC设计中电感的分析、建模与绘制（EMX电磁仿真，RFIC-GPT生成无源器件及与cadence的交互）
                      
    • 
                    
                      
  • 
                        QML 信号与信号处理器程序
                      
    • 
                    
                      
  • 
                        QScreen在Qt5.15与Qt6.8版本下的区别
                      
    • 
                    
                      
  • 
                        将OBJ或GLB文件转换为3DTiles
                      
    •