MYSQL死锁问题java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

本文解析了MySQL中死锁产生的原因及场景,特别是由于SQL语句执行后未commit导致的死锁现象。通过一个具体的Java JDBC示例展示了如何避免此类问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据库死锁分类

MySQL有三种锁的级别:页级、表级、行级。

  • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

  • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般算法:

而产生死锁的原因/场景如下:

原理:就像多线程死锁一样,比如线程ab都想写文件t,a操作完T后没有释放锁,b就没法读T了,而且a自己也没法继续下去。

场景:

1、在同一事务内先后对同一条数据进行插入和更新操作

2、多台服务器操作同一数据库

3、瞬时出现高并发现象,spring事务造成数据库死锁,后续操作超时抛出异常

4、sql语句执行后未commit

今天我遇到的是第四个场景,sql语句执行后未commit造成死锁。

java在使用jdbc时,可以设置conn.setAutoCommit(boolean b)函数关闭自动提交(b=false),然后我在关闭自动提交后,没有手动提交,表当然等着你提交了,可是你还没提交,后面的操作又来了,怎么办呢?就报错了:锁等待超时,请重启事务。奈何我一直没有提交,又如何开始事务?

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    synchronized public static void updateTableUserProfile(UserProfile userProfile){
        // 获取连接
        Connection conn = DBCtrl.getConnection();
        // 更新语句
        String sql = "UPDATE user_profile" +
                " set user_name=?, age=?, locate=?, self_intro=?" +
                " where id=?";
        try {
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, userProfile.GetUsername());
            ptmt.setInt(2, userProfile.GetAge());
            ptmt.setString(3, userProfile.GetLocate());
            ptmt.setString(4, userProfile.GetSelfIntro());
            ptmt.setInt(5, userProfile.GetId());
            System.out.println(userProfile.GetAge() + " " +userProfile.GetSelfIntro()+ " "+userProfile.GetId());
            ptmt.execute();
            conn.commit();// 我没有写这句 =========================================
            System.out.println("用户[" + userProfile.GetId() +"]成功从数据库更新基本信息.........Yes");
        }catch(SQLException e){
            e.printStackTrace();
            System.out.println("用户[" + userProfile.GetId() +"]成功从数据库更新基本信息.........No");
        }
    }

 

### Java SQL `BatchUpdateException` 锁等待超时解决方案 在Java应用程序中遇到SQL锁等待超时时,通常是因为多个事务试图同时访问并修改同一组记录。这可能导致某些事务被阻塞,直到达到最大等待时间限制,从而引发异常。 #### 增加SQL语句执行时间 一种常见的方法是通过调整JDBC连接属性中的`statementTimeout`参数来增加单个SQL语句的最大执行时间。此设置可以防止因短暂的高负载情况而导致的操作失败[^2]: ```properties # application.properties or similar configuration file spring.datasource.hikari.connection-timeout=30000 spring.datasource.hikari.validation-timeout=5000 spring.datasource.hikari.maximum-pool-size=10 spring.datasource.hikari.minimum-idle=5 spring.datasource.hikari.statement-timeout=60 # 设置为60秒 ``` 对于程序代码层面,在创建Statement对象之前也可以动态设定timeout: ```java // 动态设置SQL语句超时时间为90秒 PreparedStatement pstmt = connection.prepareStatement(sql); pstmt.setQueryTimeout(90); // 单位:秒 ``` #### 修改业务逻辑减少并发冲突 除了延长超时外,优化应用层面上的数据处理流程同样重要。考虑重构涉及频繁更新相同数据集的部分,比如采用乐观锁机制或分批次提交较小规模的变化集合以降低死锁风险。 #### 使用分布式版本控制系统管理长时间运行的任务 如果确实存在需要较长时间才能完成的工作单元,则建议引入消息队列或其他异步处理框架来进行解耦合操作;这样不仅可以缓解瞬时压力还能提高系统的整体吞吐率。 #### 数据库配置调优 针对MySQL数据库而言,还可以适当调节其内部的一些参数如innodb_lock_wait_timeout等,使得它能够更好地适应特定应用场景下的需求[^1]: ```sql SET GLOBAL innodb_lock_wait_timeout = 120; ``` 以上措施综合运用可以帮助有效应对由锁竞争引起的各种问题,并确保系统稳定性和性能表现。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值