java 超过锁等待超时；尝试重新启动事务

Lock wait timeout exceeded; try restarting transaction

提问by fuyou001

I occur Lock wait timeout exceeded error. below is exception:

我发生锁定等待超时超出错误。以下是例外：

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve commManager.updateOrderHotelInfo-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:261)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
 at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
 at $Proxy21.update(Unknown Source)
 at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:246)

spring transaction config <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean>

弹簧交易配置 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean>

use Spring annotation @Transactional,it throw exception.but change to @Transactional(propagation=Propagation.REQUIRES_NEW) ,it is ok.

使用Spring注解@Transactional，抛出异常。但是改成@Transactional(propagation=Propagation.REQUIRES_NEW)，就可以了。

I execute show engine innodb status \G on MySqlServer ,output these: --TRANSACTION 107D2F81, ACTIVE 18 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 23795, OS thread handle 0x50e8a940, query id 207416339 192.168.126.236 ppb Updating UPDATE commission_order_hotel_info_ext SET auditor_tel = '0898-88350052',

我在 MySqlServer 上执行 show engine innodb status \G，输出这些：--TRANSACTION 107D2F81, ACTIVE 18 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock( s), 撤消日志条目 1 MySQL 线程 ID 23795，操作系统线程句柄 0x50e8a940，查询 ID 207416339 192.168.126.236 ppb 更新 UPDATE Commission_order_hotel_info_ext SET audit_tel = '085905-8283

            global_commission_percent = 10.00,


            guarantee = '{\"type\":\"none\"}' 
    WHERE
        hotel_id = 10190

Trx read view will not see trx with id >= 107D2F82, sees < 107D1795 ------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:

Trx 读取视图不会看到 id >= 107D2F82 的 trx，看到 < 107D1795 ------- TRX 已经等待 18 秒来授予此锁定：

RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARYof table hms.commission_order_hotel_info_exttrx id 107D2F81 lock_mode X waiting

RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARYof table hms。commission_order_hotel_info_exttrx id 107D2F81 lock_mode X 等待

TABLE LOCK table hms.order_hotel_infotrx id 107D2F81 lock mode IX RECORD LOCKS space id 2874 page no 16 n bits 1272 index uniq_hotel_idof table hms.order_hotel_infotrx id 107D2F81 lock_mode X locks rec but not gap RECORD LOCKS space id 2874 page no 6 n bits 184 index PRIMARYof table hms.order_hotel_infotrx id 107D2F81 lock_mode X locks rec but not gap TABLE LOCK table hms.commission_order_hotel_info_exttrx id 107D2F81 lock mode IX RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARYof table hms.commission_order_hotel_info_exttrx id 107D2F81 lock_mode X waiting ---TRANSACTION 107D1795, ACTIVE 845 sec 5 lock struct(s), heap size 1248, 59 row lock(s), undo log entries 2 MySQL thread id 23819, OS thread handle 0x50dc7940, query id 207389624 192.168.126.83 ppb Trx read view will not see trx with id >= 107D1796, sees < 107D1796 TABLE LOCK table hms.order_hotel_infotrx id 107D1795 lock mode IX RECORD LOCKS space id 2874 page no 16 n bits 1272 index uniq_hotel_idof table hms.order_hotel_infotrx id 107D1795 lock_mode X locks rec but not gap RECORD LOCKS space id 2874 page no 44 n bits 232 index PRIMARYof table hms.order_hotel_infotrx id 107D1795 lock_mode X locks rec but not gap TABLE LOCK table hms.commission_order_hotel_info_exttrx id 107D1795 lock mode IX RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARYof table hms.commission_order_hotel_info_exttrx id 107D1795 lock_mode X

表锁表hms。order_hotel_infotrx id 107D2F81 锁定模式 IX RECORD LOCKS 空间 id 2874 页数 16 n 位 1272uniq_hotel_id表索引hms。order_hotel_infotrx id 107D2F81 lock_mode X 锁定 rec 但不锁定间隙 RECORD LOCKS 空间 id 2874 page no 6 n bits 184 index PRIMARYof table hms。order_hotel_infotrx id 107D2F81 lock_mode X 锁定 rec 但不锁定间隙 TABLE LOCK 表hms。commission_order_hotel_info_exttrx id 107D2F81 锁定模式 IX RECORD LOCKS 空间 id 3123 页数 3 n 位 128PRIMARY表索引hms。commission_order_hotel_info_exttrx id 107D2F81 lock_mode X 等待 ---TRANSACTION 107D1795, ACTIVE 845 sec 5 lock struct(s), heap size 1248, 59 row lock(s), undo log entry 2 MySQL thread id 23819, OS thread handle 04,207dc89 192.168.126.83 ppb Trx 读取视图将看不到 id >= 107D1796 的 trx，参见 < 107D1796 TABLE LOCK table hms。order_hotel_infotrx id 107D1795 锁定模式 IX RECORD LOCKS 空间 id 2874 页没有 16 n 位 1272uniq_hotel_id表索引hms。order_hotel_infotrx id 107D1795 lock_mode X 锁定 rec 但不锁定间隙 RECORD LOCKS space id 2874 page no 44 n bits 232 index PRIMARYof table hms。order_hotel_infotrx id 107D1795 lock_mode X 锁定 rec 但不锁定间隙 TABLE LOCK 表hms。commission_order_hotel_info_exttrx id 107D1795 锁定模式 IX RECORD LOCKS 空间 id 3123 页数 3 n 位 128PRIMARY表索引hms. commission_order_hotel_info_exttrx id 107D1795 lock_mode X

what steps to solve it

有什么步骤可以解决

回答by Robert Greathouse

The issue is caused by the connection not being able to get a lock on the row in the database. This is caused when another transaction has a lock on the same row for so long that your transaction has timed out waiting for it to complete and unlock the row. It would be of assistance for you to include your configuration, code, and database server log for that time so we can determine where the issue is coming from; as this may not be a problem caused by your code, but a issue with another application not releasing it's locks as it it should.

该问题是由连接无法锁定数据库中的行引起的。这是因为另一个事务在同一行上锁定了很长时间，以至于您的事务在等待它完成并解锁该行时超时。包含当时的配置、代码和数据库服务器日志会对您有所帮助，以便我们确定问题的来源；因为这可能不是由您的代码引起的问题，而是另一个应用程序没有按应有的方式释放它的锁的问题。

Your comment that @Transactional(propagation=Propagation.REQUIRES_NEW) makes your code work would indicate that a previous transaction, possibly one established by your method chain is not releasing the lock as it should.

您对@Transactional(propagation=Propagation.REQUIRES_NEW) 使您的代码工作的评论表明，先前的事务，可能是您的方法链建立的事务，没有按应有的方式释放锁。

Again, include your code and configuration. I saw you included your transactionManager bean declaration from your XML. But this time, actually include your code, and your complete datasource, and the beans involved in the method chain configuration. I know it may seem like work to include all your code, but if you want help, we need info. There can never be too much information in this situation. If you would like an example of how to ask a good question, look at mine.

再次，包括您的代码和配置。我看到您在 XML 中包含了您的 transactionManager bean 声明。但这一次，实际上包括您的代码，您的完整数据源，以及方法链配置中涉及的 bean。我知道包含您的所有代码似乎很有效，但是如果您需要帮助，我们需要信息。在这种情况下，信息永远不会太多。如果你想要一个如何提出好问题的例子，看看我的。