update 锁表经验分享（1）

先贴错误异常：

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error occurred while setting parameters
### SQL: UPDATE   user_info  SET      replied = ?    WHERE  id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy77.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 70 more

很明显是锁表了，按道理说表引擎为 InnoDB ，一个简单的update 语句，用的是行级锁，执行起来都是毫秒级的，怎么会出锁表这么严重的问题呢?

我做了很多测试，发现没办法重现，听说是索引的问题，我就改为根据主键ID update，但最后还是没解决。
后来仔细看这个错误提示，感觉这个似曾相识：

进行悲观锁操作的时候就有啊！

BEGIN;
SELECT * FROM user_info WHERE id = 2 FOR UPDATE;

结果：

image.png

换种思路是不是别的地方锁了，然后这边update的时候出错，与这个SQL本身没关系呢？
查看了源码，发现有个定时任务果然有类似做法：

 @Transactional(rollbackFor = Exception.class)
Thread.sleep(1000);

找到问题原因：这里开启了一个事务并查询到了对应的数据，但是线程Sleep了，事务没有提交，此时在另外一个线程去update就会一直等待，知道抛出 Lock wait timeout exceeded。

解决办法：
去掉 @Transactional(rollbackFor = Exception.class) 方法中的 Thread.sleep 写法，换成MQ或其他方式，就可以避免锁表的问题。

最后编辑于：2019.04.27 10:38:27