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

embedded/2024/12/23 17:25:09/

问题：

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.ppmy.cn/embedded/148123.html












相关文章










使用ElasticSearch实现全文检索









使用ElasticSearch实现全文检索




文章目录 全文检索任务描述技术难点任务目标实现过程1. java读取Json文件&#xff0c;并导入MySQL数据库中2. 利用Logstah完成MySQL到ES的数据同步3. 开始编写功能接口3.1 全文检索接口3.2 查询详情 4. 前端调用 全文检索 
任务描述 
在获取到数据之后如何在ES中进行数据建模&a…




阅读更多...

















设计模式の享元模板代理模式









设计模式の享元模板代理模式




文章目录 前言一、享元模式二、模板方法模式三、代理模式3.1、静态代理3.2、JDK动态代理3.3、Cglib动态代理3.4、小结 前言 本篇是关于设计模式中享元模式、模板模式、以及代理模式的学习笔记。 一、享元模式 享元模式是一种结构型设计模式&#xff0c;目的是为了相似对象的复用…




阅读更多...

















druid与pgsql结合踩坑记









druid与pgsql结合踩坑记




最近项目里面突然出现一个怪问题&#xff0c;数据库是pgsql&#xff0c;jdbc连接池是alibaba开源的druid&#xff0c;idea里面直接启动没问题&#xff0c;打完包放在centos上和windows上cmd窗口都能直接用java -jar命令启动&#xff0c;但是放到国产信创系统上就是报错&#xf…




阅读更多...

















STM32F407 | Embedded IDE01 - vscode搭建Embedded IDE开发环境（支持JLINK、STLINK、DAPLINK）









STM32F407 | Embedded IDE01 - vscode搭建Embedded IDE开发环境（支持JLINK、STLINK、DAPLINK）




导言 Embedded IDE官网:https://em-ide.com/docs/intro 我猜肯定有部分人使用SI  Keil开发STM32项目&#xff0c;也有vscode  Keil开发STM32程序。SI或vscode编写代码&#xff0c;然后切换Keil编译、下载、调试程序。有一段时间&#xff0c;我也是这么干的。但是&#xff0c;程…




阅读更多...

















分布式系统架构：服务容错









分布式系统架构：服务容错




1.为什么需要容错 分布式系统的本质是不可靠的&#xff0c;一个大的服务集群中&#xff0c;程序可能崩溃、节点可能宕机、网络可能中断&#xff0c;这些“意外情况”其实全部都在“意料之中”。故障的发生是必然的&#xff0c;所以需要设计一套健壮的容错机制来应对这些问题。 …




阅读更多...

















CSS系列（30）-- 逻辑属性详解









CSS系列（30）-- 逻辑属性详解




前端技术探索系列&#xff1a;CSS 逻辑属性详解 &#x1f310; 
致读者&#xff1a;探索国际化布局的艺术 &#x1f44b; 
前端开发者们&#xff0c; 
今天我们将深入探讨 CSS 逻辑属性&#xff0c;这个强大的国际化布局特性。 
基础概念 &#x1f680; 
逻辑属性映射 
/* 物理…




阅读更多...

















MySQL 实战：小型项目中的数据库应用（一）









MySQL 实战：小型项目中的数据库应用（一）




MySQL 简介与小型项目适配性分析 
MySQL 是一个开源的关系型数据库管理系统&#xff0c;由瑞典 MySQL AB 公司开发&#xff0c;现属于 Oracle 公司。它在 Web 应用方面被广泛使用&#xff0c;也是一种关联数据库管理系统&#xff0c;能将数据保存在不同的表中&#xff0c;以此增…




阅读更多...

















黑客术语3









黑客术语3




19、免杀 :  就是通过加壳、加密、修改特征码、加花指令等等技术来修改程序&#xff0c;  使其逃过杀毒软件的查杀。  20 、加壳 : 就是利用特殊的算法&#xff0c;将  EXE  可执行程序或者  DLL  动态连接库文件的  编码进行改变&#xff08;比如实现压缩、加密&#xff09;&a…




阅读更多...















 
 
 
 










最新文章







 





















Copyright @ 2022~2023