文档章节

java.sql.SQLException: Lock wait timeout exceeded

炁元
 炁元
发布于 2016/04/29 11:57
字数 1398
阅读 83
收藏 3

       先说我我的解决方法:找到锁住的线程然后kill掉。

mysql> kill thr_id;

       下面简单分析一下到底应该kill哪个线程。

       show processlist查看所有运行的线程,发现所有线程的状态都是sleep,不清楚这么多sleep的线程,INNODB_TRX表里也没有对应RUNNING的记录,那些线程假死了?

mysql> show full processlist;
+------+------+---------------------+----------+---------+-------+-------+-----------------------+
| Id   | User | Host                | db       | Command | Time  | State | Info                  |
+------+------+---------------------+----------+---------+-------+-------+-----------------------+
| 1172 | root | 112.124.64.64:43083 | eye      | Sleep   | 12384 |       | NULL                  |
| 1179 | root | 112.124.64.64:43086 | eye      | Sleep   |   596 |       | NULL                  |
| 1181 | root | 112.124.64.64:43087 | eye      | Sleep   | 12467 |       | NULL                  |
| 1182 | root | 112.124.64.64:43088 | eye      | Sleep   |   149 |       | NULL                  |
| 1183 | root | 112.124.64.64:43089 | eye      | Sleep   |   612 |       | NULL                  |
| 1184 | root | 112.124.64.64:43090 | eye      | Sleep   |    39 |       | NULL                  |
| 1197 | root | 112.124.64.64:43092 | eye      | Sleep   |   148 |       | NULL                  |
| 1198 | root | 112.124.64.64:43093 | eye      | Sleep   |  5415 |       | NULL                  |
| 1200 | root | 112.124.64.64:43095 | eye      | Sleep   |   581 |       | NULL                  |
| 1289 | root | 58.249.112.63:5657  | eye      | Sleep   |  1496 |       | NULL                  |
| 1325 | root | localhost           | NULL     | Query   |     0 | NULL  | show full processlist |
+------+------+---------------------+----------+---------+-------+-------+-----------------------+

       查看information_schema库中的INNODB_TRX表发现有几条记录,时间和用户反馈的时间是对上号的,id号在上结果中也有显示,表列太多,截图没截全:

       show engine innodb status发现有以下几行:

---TRANSACTION 17E34B, ACTIVE 3296 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15
MySQL thread id 1196, OS thread handle 0x7f1f0759d700, query id 140864 112.124.64.64 root
Trx read view will not see trx with id >= 17E34C, sees < 17DFA9
---TRANSACTION 17E11F, ACTIVE 6222 sec
10 lock struct(s), heap size 3112, 9 row lock(s), undo log entries 11
MySQL thread id 1199, OS thread handle 0x7f1f07458700, query id 140844 112.124.64.64 root
Trx read view will not see trx with id >= 17E120, sees < 17DFA9
---TRANSACTION 17DFAA, ACTIVE 11653 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 15
MySQL thread id 1178, OS thread handle 0x7f1f0af75700, query id 133760 112.124.64.64 root
Trx read view will not see trx with id >= 17DFAB, sees < 17DFA7
---TRANSACTION 17DFA9, ACTIVE 11655 sec
3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 15
MySQL thread id 1177, OS thread handle 0x7f1f1b682700, query id 133743 112.124.64.64 root
Trx read view will not see trx with id >= 17DFAA, sees < 17DFA7

       (TRANSACTION 17DFAA,2 lock struct(s), 0 row lock(s),0 row locks,那 2 lock struct(s)到底锁在了啥上面)【当事务超时之后show engine innodb status就不会显示此事务运行时的执行SQL,以上信息只能说明这个事务锁等待过,2016-08-20】,(不是特别理解"MySQL thread id 1196, OS thread handle 0x7f1f0759d700, query id 140864 112.124.64.64 root Trx read view will not see trx with id >= 17E34C, sees < 17DFA9",有什么提示信息难道trx_id还必须连续?")【InnoDB使用MVCC来减少锁的使用和开销,允许非阻塞读,写操作时只锁定必要的记录,MVCC工作在READ-COMMITTED,REAPEATABLE-READ隔离级别,通过TRX_ID并发控制,2016-08-20】(undo log entries 15"的意思是15条没有insert进去,还是回滚操作没有回滚回来?)【undo log记录的是没有更改之前的数据,回滚用,这里应该是回滚了的条数,2016-08-20】。

      先来尝试还原当时的情景,插入一条数据,因为未知原因事务一直未提交,获得的锁当然未释放,然后再去操作同一条数据两次,查询INNODB_TRX表,发现出现三条数据,前两条是后来更新操作的信息,trx_state字段为LOCK WAIT,第三个是没有提交事务的那条操作:


mysql> select * from INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                                                                                                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 1852B9 | LOCK WAIT | 2016-05-23 21:09:24 | 1852B9:0:3611:51      | 2016-05-23 21:09:25 |          2 |                7920 | UPDATE inspectionhistory 			SET   	          evaluation = null,  	          photoName = null 			WHERE inspectionID = '000154DD83C3313FA051' | starting index read |                 1 |                 1 |                2 |                   376 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9997 |
| 1852A8 | LOCK WAIT | 2016-05-23 21:09:13 | 1852A8:0:3611:51      | 2016-05-23 21:09:13 |          2 |                7861 | UPDATE inspectionhistory 			SET   	          evaluation = null,  	          photoName = null 			WHERE inspectionID = '000154DD83C3313FA051' | starting index read |                 1 |                 1 |                2 |                   376 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9997 |
| 18529F | RUNNING   | 2016-05-23 21:09:06 | NULL                  | NULL                |         31 |                7863 | NULL                                                                                                                                        | NULL                |                 0 |                 0 |               14 |                  3112 |              17 |                17 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9982 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+

       上表里前两个事务等待超时返回错误信息,然后INNODB_TRX表里记录自动删除,剩下未提交事务的那条记录,如下:

--- The error occurred in mit/datamodel/ibatis/inspectionhistory.xml.  
--- The error occurred while applying a parameter map.  
--- Check the updateInspectionHistory-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 18529F | RUNNING   | 2016-05-23 21:09:06 | NULL                  | NULL             |         31 |                7863 | NULL      | NULL                |                 0 |                 0 |               14 |                  3112 |              17 |                17 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9982 |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+

       当提交事务时,INNODB_TRX表里无记录。

mysql> select * from INNODB_TRX;
Empty set (0.00 sec)

       如果真是这样,那么问题来了,(为什么上面截图里面5个事务一直RUNNING状态,什么导致了它们不去提交事务。)【什么导致了5个事务一直RUNNING现在还不清原因,也还原不了了,但是前段时间遇到一种这样的情况,在一次事务没有提交时,我更改了这张表的索引字段,导致卡死,之后强制关闭命令行,再重新打开去show processlist,发现更改索引的那个事务一直RUNNING,2016-08-20】(以至于后来查询其它数据也等待超时,难道是它们之间及到了X,S,GAP锁?或者因为 Trx read view will not see?)【确实是因为没有提交的事务锁定了某些行数据,如果字段没有建索引,会锁住整张表数据,2016-08-20】

      ps:无聊时随手看了下INNIDB_TRX表信息,它的Engine竟然是Memory,其实也该想到了,只是经验太少,从没有实践机会去用Memory引擎。

+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| INNODB_TRX | MEMORY |      10 | Fixed      | NULL |           4534 |           0 |        16766732 |            0 |         0 |           NULL | 2016-05-23 22:06:37 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=3700  |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

 

© 著作权归作者所有

炁元
粉丝 0
博文 9
码字总数 13524
作品 0
西安
程序员
私信 提问
Lock wait timeout exceeded; try restarting......

mysql异常java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction: 场景: 1.开启事务,更新本地数据库; 2.调用银行接口; 3.等待银行接口返回结果,银行返回成功...

凯文加内特
2016/06/13
85
0
浅析 innodb next-key lock

record lock:记录锁,也就是仅仅锁着单独的一行 gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值,至于为什么这么定义?innodb官方定义的) next-key l...

长平狐
2012/11/01
894
0
MySQL 执行一个很长的 SQL 语句报错 —— Lock wait timeout exceeded

执行一个对数据的操作语句,需要时间很长,可能一个小时。执行报错如下: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 该怎么调整 MySQL 配置呢?...

华宰
2015/12/05
674
1
MYSQL锁等待,死锁

MYSQL一直很稳定,但是突然连接数超出最大连接数,Tomcat报 "Lock wait timeout exceeded; try restarting transaction",把tomcat重启可以恢复正常!! 能分析下这是怎么回事吗?...

OSC李强
2013/05/24
1K
7
Lock wait timeout exceeded; try restarting transac

原因是你使用的InnoDB 表类型的时候, 默认参数:innodblockwait_timeout设置锁等待的时间是50s, 因为有的锁等待超过了这个时间,所以报错. 可以把这个时间加长,或者优化存储过程,事务避免过长时...

木子丰
2014/11/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

开发函数计算的正确姿势 —— 移植 next.js 服务端渲染框架

首先介绍下在本文出现的几个比较重要的概念: 函数计算(Function Compute): 函数计算是一个事件驱动的服务,通过函数计算,用户无需管理服务器等运行情况,只需编写代码并上传。函数计算准...

阿里云云栖社区
34分钟前
1
0
Windows获取其他进程中Edit控件的内容

最近做的MFC项目中,有个获取其他进程中Edit控件内容的需求,本来以为是个很简单的问题,但是来来回回折腾了不少时间,发博记录一下。   刚开始拿到这个问题,很自然的就想到GetDlgItemTex...

WinkJie
39分钟前
0
0
Mysql数据库基于流量审计mysql-sniffer部署

Base Enviroment:CentOS release 6.5 +10.1.40-MariaDB MariaDB Server 安装完后,经过Navicat客户端工具,java web项目,linux操作系统myql客户端,三个维度(客户端)的实测。 实测结果是:...

硅谷课堂
42分钟前
0
0
Visual Paradigm 教程[UML]:如何在SoaML中建模多方服务?(下)

下载Visual Paradigm最新试用版 第三部分 - 用UML序列图指定多方编排 在多服务合同中,可以在交互图中显示各方之间的通信和编排。像UML序列图这样的交互图显示了谁调用谁以及何时进行调用。让...

xiaochuachua
49分钟前
1
0
rpm 自动依赖安装

rpm -Uvh *

Mr_Tea伯奕
52分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部