高级软件工程师带你解决MySQL死锁问题Deadlock found when trying to get lock实战案例全网唯一

高级软件工程师带你解决MySQL死锁问题Deadlock found when trying to get lock实战

前言

笔者我实际工作中，经常遇到mysql数据库死锁问题！CSDN上面也有很多大神刨析解决方法！
我在实际工作中的MySQL数据库架构很复杂，又涉及到主从多副本集群，又涉及到多个java进程进行并发级联更新操作！遇到死锁问题根本来说不能完全的避免！
我就讲讲我实际的解决思路与方法吧！

java 日志如下：

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:660)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)

一、mysql死锁是怎么造成的？

MySQL 死锁通常由以下原因造成：

1. 资源竞争：多个事务同时竞争相同的资源，比如都试图获取对方持有的锁。
2. 循环等待：事务之间形成了一种互相等待对方释放资源的循环关系。
3. 不当的事务设计：事务执行顺序不合理、执行时间过长等。
4. 并发操作冲突：在高并发环境下，多个事务对同一组数据进行操作，容易引发锁冲突导致死锁。
5. 索引使用不当：如果索引设计不合理，可能导致事务在获取锁时出现问题。

例如，事务 A 锁住了表中的一行，然后尝试对另一行加锁；同时事务 B 锁住了事务 A 想要加锁的第二行，并尝试对事务 A 锁住的第一行加锁，这样就形成了死锁。

为了避免死锁，可以合理设计事务、优化索引、尽量减少事务的阻塞时间、调整数据库配置参数等。

MySQL中的死锁是指两个或多个事务在同一时间等待对方释放锁，从而导致彼此都无法继续执行的情况。这种现象常见于并发环境中，尤其是在事务处理过程中涉及到多个资源锁定时。下面是造成MySQL死锁的一些主要原因：

1. 循环等待资源：当两个或多个事务分别锁定了对方需要的资源，形成一个“锁等待循环”，就会产生死锁。例如，事务A锁定了资源1并等待资源2，而事务B锁定了资源2并等待资源1，这样双方都在等待对方释放锁，形成死锁。

2. 并发更新同一行或多行数据：如果两个事务同时尝试更新同一行数据，并且每个事务都为更新操作申请了排他锁（写锁），那么先获得锁的事务会阻塞后来的事务，如果这些事务按照某种顺序相互等待，就会产生死锁。

3. 事务持锁时间过长：长时间持有锁的事务可能导致其他事务等待，增加死锁的风险。特别是在事务中包含大量操作或者执行耗时较长的查询时，更容易引发死锁。

4. 不恰当的锁顺序：如果多个事务按照不同的顺序锁定资源，可能会导致死锁。例如，如果一部分事务总是先锁表A后锁表B，而另一部分事务则是先锁表B后锁表A，那么在某些并发场景下可能发生死锁。

5. 批量操作与逐行处理冲突：在批量更新或删除数据时，如果其中某条记录正被另一个事务锁定，而该批量操作又在一个事务中进行，可能导致整个批次等待，与其他持有锁的事务形成死锁。

6. 索引和锁粒度：不恰当的索引设计或锁粒度过大会增加死锁的可能性。例如，如果锁住的是范围（如间隙锁），而不是单一的行，可能会无意中锁住其他事务需要的资源。

解决死锁通常需要采取一些策略，比如设置合理的事务隔离级别、优化事务逻辑减少锁的持有时间、按同一顺序锁定资源、使用死锁检测和自动解决机制（MySQL默认开启死锁检测并自动回滚持有最少行级锁的事务）等。

死锁的产生通常需要满足以下四个条件：

1. 互斥条件：一个资源每次只能被一个进程使用。
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
3. 不剥夺条件：进程已获得的资源，在未使用完之前，不能被剥夺。
4. 环路等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

在 Java 中，即使你去掉了事务注解并让 MySQL 自动提交，仍然可能出现死锁的原因有以下几点：
5. 并发操作：如果多个线程或进程同时访问和修改数据库中的数据，可能会导致死锁。例如，两个线程都需要获取两个不同的资源，但是它们获取资源的顺序不同，就可能会导致死锁。
6. 资源竞争：当多个事务或线程同时竞争同一资源时，也可能会导致死锁。例如，两个事务都需要更新同一行数据，但是它们的执行顺序不同，就可能会导致死锁。
7. 数据库设计：如果数据库设计不合理，例如存在过多的关联和约束，也可能会导致死锁。
8. MySQL 内部机制：MySQL 内部的一些机制，例如锁等待超时、死锁检测等，也可能会导致死锁。

为了避免死锁，可以采取以下措施：
9. 优化数据库设计：合理设计数据库，减少关联和约束，避免过多的并发操作。
10. 添加合适的索引：为经常用于查询、连接和排序的字段添加索引，以提高数据库的查询效率。
11. 调整事务隔离级别：根据实际需求，调整事务的隔离级别，避免过高的隔离级别导致死锁。
12. 避免长事务：尽量减少事务的执行时间，避免长时间占用资源。
13. 按照相同的顺序访问资源：多个线程或进程按照相同的顺序访问资源，可以避免死锁的发生。
14. 使用超时机制：设置合理的超时时间，避免线程或进程长时间等待资源。
15. 监控和分析死锁：定期监控数据库的死锁情况，分析死锁的原因，并采取相应的措施进行优化。

连带问题：MYSQL的一个更新语句的事务提交是不是也需要时间?

是的，MySQL中的一个更新语句执行时，如果该操作是在事务中进行的，事务的提交确实需要时间。这个时间包括但不限于以下几个步骤：

1. 执行更新操作：首先，数据库引擎（如InnoDB）需要执行更新语句，修改数据行的内容。这包括查找数据、更新索引、修改数据行等操作。

2. 记录redo log（重做日志）：InnoDB引擎会记录关于数据更改的日志，这称为redo log。即使在事务还没提交前，这部分日志也会被立即写入磁盘或缓存（取决于配置），确保事务的持久性。

3. 记录undo log（回滚日志）：同时，数据库还会记录undo log，以便在事务回滚时恢复原始数据状态或提供多版本并发控制（MVCC）的支持。

4. 事务提交处理：当调用COMMIT时，数据库会执行一系列操作来完成事务，包括但不限于标记事务状态为已提交、释放锁、持久化事务状态到事务日志（如binlog）等。

5. 刷盘确认：确保事务的持久化，相关日志和数据变更需要从内存刷到磁盘。根据MySQL的配置（如innodb_flush_log_at_trx_commit参数），这可能即时发生，也可能稍微延后，但会保证在事务提交后尽快完成。

因此，事务提交的时间取决于上述操作的执行效率，包括I/O速度、数据库负载、硬件性能、日志同步策略等因素。在低负载和良好配置的系统上，这个过程可能只需要几毫秒；而在高负载或I/O受限的环境中，可能需要更长时间，甚至导致性能瓶颈。

二、解决思路调整事务层级

在 Java 中，通过Connection对象的setTransactionIsolation()方法可以设置以下事务隔离级别：

• TRANSACTION_NONE：JDBC 驱动不支持事务。
• TRANSACTION_READ_UNCOMMITTED：允许脏读、不可重复读和幻读。
• TRANSACTION_READ_COMMITTED：禁止脏读，但允许不可重复读和幻读。
• TRANSACTION_REPEATABLE_READ：禁止脏读和不可重复读，但允许幻读。
• TRANSACTION_SERIALIZABLE：禁止脏读、不可重复读和幻读。

事务隔离级别越高，数据的一致性和可靠性就越高，但同时也会增加系统的开销和性能的影响。因此，在实际应用中，需要根据具体的业务需求和性能要求来选择合适的事务隔离级别。
以下是对这些概念的详细解释：

脏读：脏读是指一个事务读取到了另一个事务未提交的数据。比如事务 A 修改了一条数据但还未提交，此时事务 B 读取到了这个被修改但未提交的数据，如果事务 A 后续回滚了，那么事务 B 读取到的数据就是无效的、“脏”的数据。

不可重复读：是指在一个事务内，多次读取同一行数据时，得到的结果不同。这可能是因为在该事务执行过程中，其他事务对这行数据进行了修改并提交。

幻读：幻读主要体现在当一个事务在基于某个条件进行查询时，在两次查询的间隔期间，另一个事务插入或删除了符合该条件的数据，导致两次查询的结果集不同。例如事务 A 第一次查询得到了一些数据，然后事务 B 插入了一些新的数据，当事务 A 再次按照同样条件查询时，出现了之前没有的新数据，就好像出现了“幻觉”一样。

以下是一些可能的改进措施：

1. 设置合适的事务隔离级别：确保事务隔离级别与业务需求相符，避免不必要的锁冲突。可以尝试将隔离级别设置为 TRANSACTION_READ_COMMITTED 等。
2. 优化 SQL 语句：检查执行的 SQL 语句是否存在不合理的设计或可以优化的地方，减少资源竞争。
3. 添加事务超时机制：设置一个合理的事务超时时间，避免长时间持有锁导致死锁。
4. 调整业务逻辑：分析业务流程，看是否可以通过调整执行顺序或其他方式来降低死锁的可能性。
5. 使用数据库连接池：确保连接池配置合理，有效管理数据库连接。
6. 增加锁等待超时处理：在遇到锁等待时，添加超时处理逻辑，而不是一直等待导致死锁。
7. 监控和分析：建立数据库监控机制，及时发现和分析死锁情况，以便针对性地进行改进。

具体的改进方式需要根据实际情况进行综合考虑和调整。

要在 Spring Boot 中配置 MySQL 8 的事务隔离级别，可以通过spring.datasource.druid属性来进行设置。以下是一个示例配置：

# 设置事务隔离级别为 READ_COMMITTED
spring.datasource.druid.transaction-isolation-level=READ_COMMITTED

在上述配置中，将spring.datasource.druid.transaction-isolation-level属性设置为READ_COMMITTED，表示将事务隔离级别设置为读已提交。你可以根据需要将其设置为其他事务隔离级别，例如REPEATABLE_READ或SERIALIZABLE。

请注意，确保你的 MySQL 数据库服务器实际支持所配置的事务隔离级别。此外，还需要根据你的具体应用需求和数据库设计来选择合适的事务隔离级别。

另外，如果你使用的是其他数据源或连接池，可能需要参考相应的文档来了解如何设置事务隔离级别。

笔者亲自调高了Java程序的事务级别，但是问题还存在！

笔者没有调整mysql的公共的事务层级，怕出生产问题！

MySQL 8 的默认事务隔离级别为REPEATABLE-READ（可重复读）。

可以通过以下 SQL 语句查看当前会话或全局的事务隔离级别：

• 查看当前会话的事务隔离级别：

SELECT @@transaction_isolation;

• 查看全局的事务隔离级别：

SELECT @@global.transaction_isolation;

如果需要修改事务隔离级别，可以使用以下 SQL 语句：

• 设置当前会话的事务隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE;

• 设置全局的事务隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE;

请注意，不同的数据库连接池或框架可能会有不同的设置方式，上述示例是针对 MySQL 数据库的常见设置方法。在实际应用中，需要根据具体情况选择合适的事务隔离级别，并确保在事务中正确处理数据的一致性和并发问题。

三、补漏执行机制解决思路（上线可行性方案）

上述的真正生产情况，非常复杂，引起死锁的可能性，跟不空因素太多了，而且调整mysql数据库的事务层级，也可能会引起新的问题！主要是笔者这个是多个主从集群，还主从同步很多表！太复杂了！
保守方案就是重复执行，如果遇到MySQLTransactionRollbackException异常，捕获异常，连续执行几次！直到执行成功！

（一）Java代码调度案例

public int execSourceDBSqlWithManualTransaction(String sql) {
    int i = 0;
    int retryTimes = 0;
    while (true) {
        TransactionStatus status = null;
        try (TransactionTemplate transactionTemplate = new TransactionTemplate(Mapper.getTransactionManager())) {
            status = transactionTemplate.execute(new TransactionCallback<Void>() {
                @Override
                public Void doInTransaction(TransactionStatus transactionStatus) {
                    try {
                        i = Mapper.execSql(sql);
                    } catch (Exception e) {
                        if (e instanceof com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException && ((com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException) e).getMessage().contains("Deadlock found when trying to get lock; try restarting transaction")) {
                           
                            Logger.info("【ERROR】【execSql】执行 SQL：【" + sql + "】出现死锁，准备重试！");
                            continue;
                        } else {
                            
                            Logger.info("【ERROR】【execDBSql】执行 SQL：【" + sql + "】出现数据库错误！");
                           
                        }
                    }
                    return null;
                }
            });
        }
        if (status.isCompleted()) {
            // 提交成功
            break;
        }
        retryTimes++;
        if (retryTimes > 10) { // 设置一个合理的最大重试次数
            Logger.error("【ERROR】【execSourceDBSql】执行 SQL：【" + sql + "】多次重试后仍失败！");
            break;
        }
    }
    return i;
}

（二）Spring的@Retryable注解补漏调度机制

import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Recover;
import org.springframework.retry.annotation.Retryable;
import org.springframework.transaction.annotation.Transactional;

@Service
public class YourService {

    @Transactional
    @Retryable(value = MySQLTransactionRollbackException.class, maxAttempts = 3, backoff = @Backoff(delay = 1000))
    public int execSourceDBSqlWithRetry(String sql) {
        try {
            int result = Mapper.execSql(sql);
            // 确保事务提交，可以通过在finally块中添加一个显式的flush和commit操作，
            // 但实际上在Spring中，如果方法结束且没有抛出异常，事务会自动提交。
            // 这里不需要额外操作，因为我们依赖Spring的事务管理。
            return result;
        } catch (Exception e) {
            // 记录错误逻辑保持不变
            ErrorSqlRecord.getErrsql_list().add(sql);
            Logger.info("【ERROR】【execSourceDBSqlWithRetry】执行SQL：【" + sql + "】出现数据库错误！");
      
            throw e; // 重新抛出异常以便触发重试
        }
    }

    // 重试结束后恢复方法，当达到最大重试次数后执行
    @Recover
    public int recover(MySQLTransactionRollbackException ex, String sql) {
        Logger.error("【RETRY FAILED】【execSourceDBSqlWithRetry】执行SQL：【" + sql + "】重试3次后仍失败！");
        // 根据需要处理最终失败的情况，比如记录日志或发送告警
        return -1; // 或者其他表示失败的返回值
    }
}

（三）spring切面统一在mybatis的mapper层方法捕获异常增加补漏机制

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.stereotype.Component;

import java.util.Date;

@Aspect
@Component
public class MapperAspect {

    @Around("execution(* com.xxx.Mapper.*(..))")
    public Object aroundMethod(ProceedingJoinPoint joinPoint) throws Throwable {
        long start = System.currentTimeMillis();
        Date startDate = new Date(start);

        try {
            Object result = joinPoint.proceed();

            long end = System.currentTimeMillis();
            Date endDate = new Date(end);

            // 在这里记录开始时间和结束时间

            return result;
        } catch (Exception e) {
            int retryCount = 3;
            while (retryCount > 0) {
                try {
                    return joinPoint.proceed();
                } catch (Exception ex) {
                    retryCount--;
                }
            }
            throw new RuntimeException("执行三次仍失败", e);
        }
    }
}

spring 切面用法，在调用每个数据库更新方法之后，统一加异常捕获3次重复执行，如果都失败！向上抛出异常！

（四）mysql数据库层索引定制(或者采用不同查询条件进行更新)

笔者实际工作中发现，如果是更新频繁，并且更新的条件语句差不多，更新语句采用的索引一样，并发执行，或者频繁调用就更容易出现死锁的情况！
当需要在 MySQL 8 中强制使用索引时，可以使用FORCE INDEX子句。以下是一个示例，假设有两个表：users和orders，其中orders表中有一个外键user_id关联到users表的id字段。现在要查询orders表中某个用户的订单信息，并强制使用users表的索引：

SELECT * FROM orders FORCE INDEX (user_id) WHERE user_id = 1;

在上述示例中，通过FORCE INDEX (user_id)子句强制使用了users表的索引。这样可以提高查询的性能，特别是在数据量较大的情况下。

请注意，使用FORCE INDEX时需要谨慎，确保强制使用的索引确实能够提高查询性能。如果索引不恰当，可能会导致查询性能下降。此外，还需要根据具体的数据库结构和查询需求来选择合适的索引。

业务1经常更新操作强制索引index_1
业务2经常更新操作强制索引index_2
业务3经常更新操作强制索引index_3

还要一种方法就是更新操作的过滤条件，不同的业务字段条件区分一下，尽量调用不同的索引！
业务A 用where条件 字段1 字段2 字段3
业务B 用where条件 字段1 字段2 字段3 字段4

不同的业务更新同一个表，条件变化一下，尽量用到不同的索引划分，可以减少一些锁表的情况！

（五）MySQL 8 实现乐观锁和悲观锁实现同步执行更新语句（并发性能很差）

以下是一个使用 MySQL 8 实现乐观锁和悲观锁的示例：

1. 乐观锁：

乐观锁是一种基于版本号或时间戳的并发控制机制。在读取数据时，记录数据的版本号或时间戳，在更新数据时，检查版本号或时间戳是否与读取时一致，如果一致则进行更新，否则认为数据已经被其他事务修改，更新失败。

以下是一个使用乐观锁的示例：

-- 创建表
CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    version INT
);

-- 插入数据
INSERT INTO your_table (name, version) VALUES ('John Doe', 1);

-- 读取数据并获取版本号
SELECT * FROM your_table WHERE id = 1;

-- 更新数据
UPDATE your_table SET name = 'Jane Doe', version = version + 1 WHERE id = 1 AND version = 1;

在上述示例中，我们在表中添加了一个version字段来记录数据的版本号。在读取数据时，同时获取版本号。在更新数据时，使用WHERE子句检查版本号是否与读取时一致，如果一致则进行更新，否则更新失败。

2. 悲观锁：

悲观锁是一种基于锁定的并发控制机制。在读取数据时，锁定数据，直到事务结束，其他事务无法访问被锁定的数据。

以下是一个使用悲观锁的示例：

-- 开始事务
BEGIN;

-- 读取数据并锁定
SELECT * FROM your_table WHERE id = 1 FOR UPDATE;

-- 进行数据操作
更新操作
-- 提交事务
COMMIT;

在上述示例中，我们使用SELECT...FOR UPDATE语句来锁定数据。在事务结束之前，其他事务无法访问被锁定的数据。

请注意，悲观锁会降低并发性能，因为它会锁定数据，阻止其他事务访问。因此，在实际应用中，应根据具体情况选择合适的并发控制机制。

四、方法总结

总结一下，算法代码调度，补漏的算法机制，确定语句是执行成功的，如果执行失败了，就重复执行，直到成功！ 完全避免死锁，笔者在实际工作中觉得不大现实！因为不可控制的因素太多了！我个人觉得这种方案的可靠性比较强！安全性比较高！事故率低，也就是占更多时间资源！

我们每个开发人员，技术体系也是局限的，比如JAVA工程师比较熟悉程序调度，DBA管理员就比较熟悉数据库运作，但是要做到全部精通，全栈全懂，其实现实中没有这么完美的研发工程师！就算是技术总监也许更擅长架构设计！具体的实际问题也得实际有经验的研发解决！

全栈不存在，完美的人也不存在！完美的项目，完美的代码，完美的世界都不存在，接受不完美吧！

五、笔者简介

国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！