乐观锁加重试,并发更新数据库一条记录导致:Lock wait timeout exceeded 2023-05-22 11:27 56阅读 0赞 ### 背景: ### * mysql数据库,用户余额表有一个version(版本号)字段,作为乐观锁。 * 更新方法有事务控制: @Transactional(rollbackFor = Exception.class) * 更新时,比对版本号,如果版本号不一致,则更新失败。 * 有重试机制,如果更新失败,则查询最新版本号,再次更新,重试超过5次,报错退出。 * 更新的核心方法: public boolean updateUserAccount(Long userId, int amount) { boolean retryable; int attemptNumber = 0; do { // 查询最新版本号 UserAccount userAccount = accountMapper.selectByPrimaryKey(userId); long oldVersion = userAccount.getVersion(); // 更新 boolean success = accountMapper.updateBalance(amount, new Date(), userId, oldVersion) > 0; if (success) { return true; } else { attemptNumber++; retryable = attemptNumber < 5; if (attemptNumber == 5) { log.error("超过最大重试次数"); break; } try { Thread.sleep(300); } catch (InterruptedException e) { log.error(e); } } } while (retryable); return false; } * 更新语句: UPDATE user_account SET balance = balance - #{amount,jdbcType=INTEGER}, update_time = #{updateTime,jdbcType=TIMESTAMP}, version = #{version,jdbcType=BIGINT} + 1 WHERE balance > #{amount,jdbcType=INTEGER} AND user_id = #{userId,jdbcType=BIGINT} AND version = #{version,jdbcType=BIGINT}; 在并发更新时,报异常:Lock wait timeout exceeded ### 分析: ### 根据日志分析出: 线程a、b几乎同时到达 线程a查询版本号:856 线程a更新数据库:成功 数据库当前版本号:857 线程b查询到的版本号:856(实际已不是最新) 线程b更新数据库:失败 线程b重试,查询版本号:856 线程b更新数据库:失败 。。。 线程b超过重试次数,退出 线程b重试的过程中,又有其他线程到来,比如c,d,e 线程c查询版本号:857 线程c更新数据库:阻塞,因为b拿到锁一直在重试 线程d查询版本号:857 线程d更新数据库:阻塞,因为b拿到锁一直在重试 线程b超次数退出后,c,d,e争抢锁 d拿到锁,更新数据库:成功 数据库当前版本号:858 线程c查询到的版本号:857(实际已不是最新) 线程c更新数据库:失败 线程c重试,查询版本号:857 线程c更新数据库:失败 。。。 线程c超过重试次数,退出 某个事务对应的线程一直抢不到执行的机会,就一直等待。 最后因事务执行时间超过mysql默认的锁等待时间(50s),就会报出:Lock wait timeout exceeded 为什么线程读不到最新的版本号呢?原来是用到了事务,且mysql默认事务隔离级别Repeatable Read, 把隔离级别改为READ_COMMITTED,问题解决。 @Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_COMMITTED) 分析了这么多,解决问题其实只需要一行代码。 分析一:为什么事务隔离级别Repeatable Read会导致读不到最新版本号呢? 先来看这样一段话: consistent read (一致性读),InnoDB用多版本来提供查询数据库在某个时间点的快照。 如果隔离级别是REPEATABLE READ,那么在同一个事务中的所有一致性读 都读的是 事务中第一个这样的读 读到的快照 即:快照读; 如果是READ COMMITTED,那么一个事务中的每一个一致性读 都会读到它自己刷新的快照版本,即:当前读。这段话就解释清楚了为什么线程B第一次读到版本号856,后面重试时读到的版本号仍然是856。 首先,线程A和B几乎同时到达,都读到了当前数据库版本号856; 线程A抢到了更新MySQL的行锁,将版本号加1更新为857; 线程B在线程A更新数据库的同时,也没闲着,它一直在重试,尝试读到最新版本号,可是直到线程A成功更新完数据库,它也没拿到最新版本号。 原因就在于当前事务隔离级别为REPEATABLE READ。 这个隔离级别会让程序在同一事务中,多次读到的都是相同的数据,也就是保证可重复读。 所以,只要还在这个事务里,无论B怎么重试,它都读不到最新数据。 可以设想一下,如果B重试过程中读到了最新版本号呢? 也就是B一开始读到版本号856,后面重试时读到线程A更新入库的最新版本号857,那么就会导致一个问题——不可重复读。 而这正是REPEATABLE READ要解决的问题。 对于本业务场景来说,READ_COMMITTED虽然会导致不可重复读,但是这里恰好利用了这个特性,可以让线程在事务里不断重试过程中读到其它线程更新的最新的版本号。 这样就避免了线程反复重试,占有CPU资源,以及争抢数据库锁。 分析二:为什么会Lock wait timeout exceeded异常? 因为是每个线程到来后,都各自起一个事务。 假设,某个线程C读到的是旧版本号,拿着旧版本号去更新数据库,发现版本号不匹配,更新失败; 然后,进入重试阶段,正常逻辑是 再次尝试读取新版本号,然后再去更新数据库; 然而,实际情况却不是这样,在C再次去更新数据库时被挂起等待,因为此时有其它线程在执行update语句; 不幸的是,其它线程也是反复重试,时间在流逝,终于轮到C执行update,结果仍然由于版本号是旧的,更新失败; C准备继续重试,因为还没有超过重试最大次数; 不幸又来了,C准备update时,又有其它线程插进来执行update语句; 就这样,C所在的事务消耗的时间越来越久,终于到达了MySQL的极限——崩了!
相关 Lock wait timeout exceeded; try restarting transaction 一:问题分析 : 今天程序里报的错: java.sql.BatchUpdateException: Lock wait timeout exceeded; try resta 迷南。/ 2023年11月26日 00:43/ 0 赞/ 18 阅读
相关 乐观锁加重试,并发更新数据库一条记录导致:Lock wait timeout exceeded 背景: mysql数据库,用户余额表有一个version(版本号)字段,作为乐观锁。 更新方法有事务控制: @Transactional(rollb 朱雀/ 2023年05月22日 11:27/ 0 赞/ 57 阅读
相关 Lock wait timeout exceeded; try restarting transaction 一 概述 今天在本地debug代码的时候,没有断点执行完毕,导致测试环境前端调用测试服务器服务的时候出现了问题: 1. org.springframework.jdbc 悠悠/ 2022年09月08日 14:51/ 0 赞/ 83 阅读
相关 java.sql.SQLException: Lock wait timeout exceeded 先说我我的解决方法:找到锁住的线程然后kill掉。 mysql> kill thr_id; 下面简单分析一下到底应该kill哪个线程。 r囧r小猫/ 2022年07月11日 01:46/ 0 赞/ 66 阅读
相关 MySQL 事务没有提交导致 锁等待 Lock wait timeout exceeded 事物锁 java.lang.Exception: \\\ Error updating database. Cause: java.sql.SQLException: Lock 墨蓝/ 2022年06月08日 09:25/ 0 赞/ 61 阅读
相关 数据库锁等待超时 java.sql.SQLException: Lock wait timeout exceeded 数据库等待锁定超时 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 比眉伴天荒/ 2022年06月07日 11:09/ 0 赞/ 116 阅读
相关 1205 Lock wait timeout exceeded try restarting transaction 早上执行语句: update report\_user\_info set cell = replace(cell,'"','') where id<10000; 蔚落/ 2022年05月29日 08:06/ 0 赞/ 80 阅读
相关 Lock wait timeout exceeded; try restarting transaction 锁等待超时 Lock wait timeout exceeded; try restarting transaction,是当前事务在等待其它事务释放锁资源造成的 绝地灬酷狼/ 2022年04月10日 04:10/ 0 赞/ 188 阅读
相关 Lock wait timeout exceeded; try restarting transaction(mysql事务锁) 现场环境客户要求删数据(界面没法直接操作),于是直接在数据库进行查询删除了,删完发现界面依然能查到删除后的数据,又用sql语句进行删除,发现报了错:Lock wait time 红太狼/ 2022年01月20日 02:45/ 0 赞/ 143 阅读
相关 MySQL事务锁问题-Lock wait timeout exceeded 问题现象: 接口响应时间超长,耗时几十秒才返回错误提示,后台日志中出现Lock wait timeout exceeded; try restarting transac 今天药忘吃喽~/ 2021年12月14日 09:49/ 0 赞/ 157 阅读
还没有评论,来说两句吧...