【弄nèng - 化繁为简】多线程下@Transactional失效

一. @Transactional失效

@Transactional失效的场景有很多种，感兴趣的研究下，文章很多，本文着重说明多线程下Spring事务注解@Transactional的场景。

问题

在一个方法中两次更新同一条记录，报错如下：

### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy127.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:63)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
	at com.sun.proxy.$Proxy172.updateById(Unknown Source)
	at com.ws.cloud.modules.yy.service.impl.TestServiceImpl.updateTestById(TestServiceImpl.java:59)
	at com.ws.cloud.modules.yy.service.impl.TestServiceImpl$1.run(TestServiceImpl.java:38)
	at java.lang.Thread.run(Thread.java:745)

代码

    @Transactional
    public void testTransactional() {
        System.out.println("1.====更新开始====");
        updateTestById("司马缸");
        System.out.println("1.====更新结束====");

        Thread innerThread = new Thread(new Runnable() {
            @Override
            public void run() {
                System.out.println("2.====更新开始====");
                updateTestById("司马缸砸缸");
                System.out.println("3.====更新结束====");
            }
        });
        innerThread.start();

        try {
            // 主线程睡60s
            Thread.sleep(60000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        System.out.println("====主线程结束====");
    }
    
    public void updateTestById(String name) {
        TestEntity entity = new TestEntity();
        entity.setId(1);
        entity.setName(name);
        testMapper.updateById(entity);
    }

运行以上代码的详细日志如下：

1.====更新开始====
2021-11-02 17:02:59.080 default [http-nio-8082-exec-1] DEBUG c.ws.cloud.modules.yy.mapper.TestMapper.updateById - ==>  Preparing: UPDATE test SET name=? WHERE id=? 
2021-11-02 17:02:59.782 default [http-nio-8082-exec-1] DEBUG c.ws.cloud.modules.yy.mapper.TestMapper.updateById - ==> Parameters: 司马缸(String), 1(Integer)
2021-11-02 17:02:59.793 default [http-nio-8082-exec-1] DEBUG c.ws.cloud.modules.yy.mapper.TestMapper.updateById - <==    Updates: 1
1.====更新结束====
2.====更新开始====
2021-11-02 17:03:24.592 default [Thread-7] DEBUG c.ws.cloud.modules.yy.mapper.TestMapper.updateById - ==>  Preparing: UPDATE test SET name=? WHERE id=? 
2021-11-02 17:03:24.594 default [Thread-7] DEBUG c.ws.cloud.modules.yy.mapper.TestMapper.updateById - ==> Parameters: 司马缸砸缸(String), 1(Integer)
Exception in thread "Thread-7" org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in com/ws/cloud/modules/yy/mapper/TestMapper.java (best guess)
### The error may involve com.ws.cloud.modules.yy.mapper.TestMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE test  SET name=?  WHERE id=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy127.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:63)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:62)
	at com.sun.proxy.$Proxy172.updateById(Unknown Source)
	at com.ws.cloud.modules.yy.service.impl.TestServiceImpl.updateTestById(TestServiceImpl.java:59)
	at com.ws.cloud.modules.yy.service.impl.TestServiceImpl$1.run(TestServiceImpl.java:38)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:483)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy257.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:483)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy255.update(Unknown Source)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:54)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:483)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 8 more
====主线程结束====

分析

查看日志很明显在进行第二次更新的时候锁住了，一直等待锁。因为两次更新不在一个事务里。第二次一直等待第一次更新释放锁。

原因

Spring实现事务的原理是通过ThreadLocal把数据库连接绑定到当前线程中，同一个事务中数据库操作使用同一个jdbc connection，新开启的线程获取不到当前jdbc connection。

结论

在业务逻辑上进行更改，避免大事务套小事务，操作同一条记录。