记录第一次因为数据库事务产生的BUG

背景是在开发12306项目中，一键生成座位接口中，想着使用线程池优化一下

    @Override
    @Transactional
    public void genTrainSeat(String trainCode) throws InterruptedException {
        //使用线程池进行优化
        //根据CPU核心数创建一个固定大小的线程池
        ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
        //使用计数器（需要用阻塞的方式让开启的所有线程都执行完成才结束整个的任务方法，等待下一轮任务）
        CountDownLatch countDownLatch = new CountDownLatch(trainCarriages.size());

        //循环生成每个车厢的座位
        trainCarriages.forEach(carriage -> {
            executorService.execute(() -> {
                try {
                    循环生成座位的代码（太多，不进行展示，会向数据库插入很多条记录）
                } finally {
                    //计数器-1，直到把计数器减为0就不再阻塞
                    //try,finally的目的是无论是中途return还是正常执行，最后计数器都会-1
                    countDownLatch.countDown();
                }
            });
        });
        //阻塞，直到计数器减为0，阻塞就放行(需要指定最大限度的等待时间，阻塞最多等待一定的时间后就解除阻塞)
        countDownLatch.await(30, TimeUnit.MINUTES);
        executorService.shutdownNow();
    }

然后调试的时候就给我报错了，异常信息如下：

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 file [/Users/jalen/idea_project/train/train-business/target/classes/mapper/TrainSeatMapper.xml]
### The error may involve com.jalen.train.business.mapper.TrainSeatMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into train_seat (id, train_code, carriage_index,                             `row`, col, seat_type, carriage_seat_index,                             create_time, update_time)     values (?, ?, ?,             ?, ?, ?,             ?,             ?, ?)
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:272)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
	at jdk.proxy2/jdk.proxy2.$Proxy82.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at jdk.proxy9/jdk.proxy9.$Proxy194.insert(Unknown Source)
	at com.jalen.train.business.service.impl.TrainSeatServiceImpl.lambda$genTrainSeat$0(TrainSeatServiceImpl.java:164)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at com.jalen.train.business.service.impl.TrainSeatServiceImpl.lambda$genTrainSeat$1(TrainSeatServiceImpl.java:152)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:840)
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.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at jdk.internal.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
	at jdk.proxy4/jdk.proxy4.$Proxy124.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:48)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:75)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425)

刚开始还以为sql问题，往下看发现不对劲，MySQL事务回滚异常！！！

然后开始问chat，得出的结论事务范围过大或事务中包含耗时操作，导致锁持有时间过长。

解决方案是减少锁持有时间：确保事务尽可能短，避免在事务中执行耗时操作。

解决并测试成功后的代码：

public void genTrainSeat(String trainCode) throws InterruptedException {
        // 使用线程池进行优化
        ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
        CountDownLatch countDownLatch = new CountDownLatch(trainCarriages.size());

        // 循环生成每个车厢的座位
        for (TrainCarriage carriage : trainCarriages) {
            executorService.execute(() -> {
                try {
                    insertSeatsForCarriage(trainCode, carriage, now);
                } finally {
                    countDownLatch.countDown();
                }
            });
        }

        // 阻塞，直到计数器减为0
        countDownLatch.await(30, TimeUnit.MINUTES);
        executorService.shutdown();
        executorService.awaitTermination(30, TimeUnit.MINUTES);
    }

    @Transactional
    public void insertSeatsForCarriage(String trainCode, TrainCarriage carriage, Date now) {
        ......
}

总结：因为在一个事务中包含了太多的插入操作，导致锁竞争和锁超时问题。这种情况在高并发环境下尤为明显，因为多个线程同时尝试插入数据，可能会导致数据库锁定资源的时间过长，从而引发锁等待超时。

孩子记住啦。恭喜自己入职第41天！！！🎉🎉🎉