Lock wait timeout exceeded; try restarting transac

问题:

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>

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

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',

            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:

RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARY of table hms.commission_order_hotel_info_ext trx id 107D2F81 lock_mode X waiting

TABLE LOCK table hms.order_hotel_info trx id 107D2F81 lock mode IX RECORD LOCKS space id 2874 page no 16 n bits 1272 index uniq_hotel_id of table hms.order_hotel_info trx id 107D2F81 lock_mode X locks rec but not gap RECORD LOCKS space id 2874 page no 6 n bits 184 index PRIMARY of table hms.order_hotel_info trx id 107D2F81 lock_mode X locks rec but not gap TABLE LOCK table hms.commission_order_hotel_info_ext trx id 107D2F81 lock mode IX RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARY of table hms.commission_order_hotel_info_ext trx 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_info trx id 107D1795 lock mode IX RECORD LOCKS space id 2874 page no 16 n bits 1272 index uniq_hotel_id of table hms.order_hotel_info trx id 107D1795 lock_mode X locks rec but not gap RECORD LOCKS space id 2874 page no 44 n bits 232 index PRIMARY of table hms.order_hotel_info trx id 107D1795 lock_mode X locks rec but not gap TABLE LOCK table hms.commission_order_hotel_info_ext trx id 107D1795 lock mode IX RECORD LOCKS space id 3123 page no 3 n bits 128 index PRIMARY of table hms.commission_order_hotel_info_ext trx id 107D1795 lock_mode X

what steps to solve it

回答1:

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.

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.