【mysql】Deadlock found when trying to get lock；出现原因以及解决方案

🕗 发布于 2024-12-10 03:34 mysql 数据库

文章目录

1. 死锁基础

什么是死锁？

死锁是多个进程或事务在执行过程中，因互相等待对方持有的资源而无法继续执行的一种状态。在数据库管理系统中，这种情况通常涉及对数据行的锁。每个事务持有一部分资源并尝试获取其他事务持有的资源，如果资源请求形成一个循环等待链，那么这些事务将无法向前推进，从而形成死锁。

死锁在数据库中的表现

在MySQL数据库中，死锁主要表现为以下几种情况：

互锁：两个或多个事务各自持有一部分资源，并试图获取对方已锁定的其他资源，导致彼此等待对方释放资源。
等待时间过长：事务在等待获取资源时超过了系统设定的超时时间，可能被系统自动终止。
事务回滚：数据库管理系统检测到死锁时，为了打破死锁，通常会选择一个或多个事务进行回滚。

在实际操作中，死锁的发生通常伴随着性能下降，因为事务等待锁的时间增长会导致响应时间变长。此外，频繁的死锁和事务回滚也会影响数据库的整体效率和稳定性。

2. MySQL中死锁的成因

事务并发与锁机制

查看当前锁定状态:
```
SHOW ENGINE INNODB STATUS;
```

查看正在运行的事务:

SELECT * FROM information_schema.innodb_trx;

具体操作导致死锁的示例

模拟死锁情况:
假设有两个事务同时操作两个表 table1 和 table2。

事务 1:

START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE column2 = 'some_value';
-- 延迟或等待
UPDATE table2 SET column1 = value1 WHERE column2 = 'some_value';
COMMIT;

事务 2:

START TRANSACTION;
UPDATE table2 SET column1 = value1 WHERE column2 = 'some_value';
-- 延迟或等待
UPDATE table1 SET column1 = value1 WHERE column2 = 'some_value';
COMMIT;

这两个事务在没有正确的锁定顺序时可能会导致死锁。

索引和锁定策略对死锁的影响

查看表的索引:
```
SHOW INDEXES FROM table_name;
```
索引缺失：如果更新或查询操作没有合适的索引支持，MySQL可能需要扫描更多的行并锁定它们，从而增加了死锁的风险。

分析锁定类型:

SELECT * FROM information_schema.innodb_locks;

锁升级：在某些情况下，较小粒度的锁（如行锁）可能会因为锁太多而升级为更大粒度的锁（如表锁），这也可能导致死锁。

3. 如何检测和分析死锁

使用 `SHOW ENGINE INNODB STATUS` 分析死锁

在MySQL中，你可以使用 SHOW ENGINE INNODB STATUS 命令来获取关于InnoDB存储引擎的当前状态，包括死锁的详细信息。以下是如何使用这个命令以及如何解读输出中的死锁信息的步骤：

执行命令:
在MySQL命令行工具中，输入以下命令：
```
SHOW ENGINE INNODB STATUS;
```
查找死锁信息:
在命令的输出中，找到 LATEST DETECTED DEADLOCK 部分。这部分将包含最近一次检测到的死锁的信息，例如涉及的事务、锁定的资源、等待的时间等。
分析输出:
死锁部分会显示两个事务试图获取对方已持有的锁。输出将包括事务的ID、相关的SQL语句、锁定的类型和等待的对象。

日志文件中的死锁信息

MySQL的错误日志也可以提供关于死锁的信息。你需要确保MySQL配置了适当的日志记录级别。以下是如何配置和查看日志中的死锁信息：

配置MySQL:
确保在MySQL的配置文件（通常是 my.cnf 或 my.ini）中开启了错误日志：
```
[mysqld]
log_error = /var/log/mysql/mysql_error.log
```
查看日志:
使用文本编辑器或命令行工具查看日志文件：
```
tail -f /var/log/mysql/mysql_error.log
```
使用 grep 命令来过滤死锁相关的日志：
```
grep 'DEADLOCK' /var/log/mysql/mysql_error.log
```

第三方工具的使用

除了MySQL自带的工具和日志，还有一些第三方工具可以帮助检测和分析死锁，如 Percona Toolkit。

Percona Toolkit:
Percona Toolkit 是一套开源的工具集，用于监控、管理和优化MySQL性能。其中的 pt-deadlock-logger 工具可以用来监控和记录死锁事件。

下载和安装:
你可以从Percona的官方网站下载Percona Toolkit：
Percona Toolkit Official Download

使用 pt-deadlock-logger:
```
pt-deadlock-logger --user=root --ask-pass --host=localhost --create-dest-table --dest D=mydatabase,t=deadlocks
```
这个命令会要求输入数据库密码，然后开始监控和记录死锁到指定的数据库表中。

4. 解决和预防死锁的策略

优化SQL语句和索引设计

优化SQL查询:

减少锁竞争，通过优化查询语句来减少需要锁定的行数。

-- 优化前
SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2023-01-01';

-- 优化后（确保有适当的索引）
SELECT order_id, total_amount FROM orders WHERE customer_id = 101 AND order_date >= '2023-01-01';

改进索引设计:

添加或修改索引以减少锁的粒度，提高查询效率。

-- 添加索引以支持查询
ALTER TABLE orders ADD INDEX idx_customer_order (customer_id, order_date);

适当的事务大小和锁策略

控制事务大小:

保持事务尽可能小，减少锁定资源的时间。

START TRANSACTION;
-- 执行必要的更新
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

锁策略:

明确锁的顺序，避免不同事务中锁的交叉请求。

-- 伪代码
BEGIN TRANSACTION;
SELECT * FROM resource1 FOR UPDATE;
SELECT * FROM resource2 FOR UPDATE;
-- 更新操作
UPDATE resource1 SET data = new_data;
UPDATE resource2 SET data = new_data;
COMMIT;

应用程序层面的解决方案

超时设置:

在应用程序中设置超时，避免无限等待锁。

// 伪代码
try {
    if (lock.tryLock(10, TimeUnit.SECONDS)) {
        try {
            // 执行事务操作
        } finally {
            lock.unlock();
        }
    } else {
        // 处理锁获取失败的情况
    }
} catch (InterruptedException e) {
    Thread.currentThread().interrupt();
}

死锁检测与恢复:

实现死锁检测逻辑，并在检测到死锁时进行恢复。

// 伪代码
while (true) {
    try {
        // 尝试获取所有必要的锁
        if (getAllLocks()) {
            try {
                // 执行事务
            } finally {
                releaseAllLocks();
            }
            break;
        }
    } catch (DeadlockDetectedException e) {
        handleDeadlock();
        continue;
    }
}

通过这些策略，可以在不同层面上有效地解决和预防死锁。优化SQL语句和索引可以减少数据库层面的锁竞争，适当控制事务大小和锁策略可以减少死锁的机会，而应用程序层面的策略则提供了更多灵活性和控制权，以动态处理潜在的死锁问题。

5. 案例研究

这个示例将模拟一个简单的电子商务场景，其中包括库存管理和账户余额更新的操作。

实际案例分析

假设有两个线程，分别代表两个用户在电子商务平台上几乎同时进行结账操作。每个用户的结账操作都涉及到检查库存和更新账户余额的步骤。

public class DeadlockDemo {

    private static final Object lockInventory = new Object();
    private static final Object lockBalance = new Object();

    public static void main(String[] args) {
        Thread user1 = new Thread(() -> {
            try {
                System.out.println("User 1 is trying to lock inventory");
                synchronized (lockInventory) {
                    System.out.println("User 1 locked inventory");
                    Thread.sleep(100); // Simulate operation delay
                    System.out.println("User 1 is trying to lock balance");
                    synchronized (lockBalance) {
                        System.out.println("User 1 locked balance");
                    }
                }
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        });

        Thread user2 = new Thread(() -> {
            try {
                System.out.println("User 2 is trying to lock balance");
                synchronized (lockBalance) {
                    System.out.println("User 2 locked balance");
                    Thread.sleep(100); // Simulate operation delay
                    System.out.println("User 2 is trying to lock inventory");
                    synchronized (lockInventory) {
                        System.out.println("User 2 locked inventory");
                    }
                }
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        });

        user1.start();
        user2.start();
    }
}

在上面的代码中，如果两个线程（用户）几乎同时运行，它们可能会彼此等待对方释放锁，从而导致死锁。

修改策略后的结果

为了解决这个问题，我们可以通过确保所有线程按相同的顺序获取锁来避免死锁。

public class DeadlockResolvedDemo {

    private static final Object lockInventory = new Object();
    private static final Object lockBalance = new Object();

    public static void main(String[] args) {
        Thread user1 = new Thread(() -> {
            try {
                System.out.println("User 1 is trying to lock inventory");
                synchronized (lockInventory) {
                    System.out.println("User 1 locked inventory");
                    Thread.sleep(100); // Simulate operation delay
                    System.out.println("User 1 is trying to lock balance");
                    synchronized (lockBalance) {
                        System.out.println("User 1 locked balance");
                    }
                }
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        });

        Thread user2 = new Thread(() -> {
            try {
                System.out.println("User 2 is trying to lock inventory");
                synchronized (lockInventory) {
                    System.out.println("User 2 locked inventory");
                    Thread.sleep(100); // Simulate operation delay
                    System.out.println("User 2 is trying to lock balance");
                    synchronized (lockBalance) {
                        System.out.println("User 2 locked balance");
                    }
                }
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        });

        user1.start();
        user2.start();
    }
}

在修改后的示例中，无论是用户1还是用户2，都首先尝试锁定库存，然后锁定余额。这种一致的锁定顺序减少了死锁的风险，使系统更加稳定和可靠。

原文地址：https://blog.csdn.net/qq_42320804/article/details/144294642

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：网络安全-使用HTTP动词篡改的认证旁路
下一篇：算法基础学习Day1(双指针)

.NET(C#) 如何配置用户首选项及保存用户设置
.NET(C#) 如何配置用户首选项及保存用户设置
阅读更多2024-12-14
【最新】北大数字普惠金融指数数据集-省市县（2011-2023年）
郭峰,王靖一,王芳,孔涛,张勋,程志云.测度中国数字普惠金融发展:指数编制与空间特征[J].经济学(季刊),2020,19(04):1401-1418.时间跨度：省级和城市级指数时间跨度为2011-2
阅读更多2024-12-14
GESP202412 四级【Recamán】题解（AC）
a11ak−1−kkakak−1−kak−1k小杨想知道 Recamán 数列的前n项从小到大排序后的结果。手动计算非常困难，小杨希望你能帮他解决这个问题。
阅读更多2024-12-14
IDEA遇到EasyConnect中的网络资源无法访问的问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/wanshanyu_/article/de
阅读更多2024-12-14
双目摄像头标定方法
此时已经完成标定，左下角为反投影误差，右边为外参可视化。将双目左右目拍的图像上传（左右目最好不少于20张）此时回到主页面，即可看到成功导出。把这些误差大的删除即可。
阅读更多2024-12-14
Servlet、omcat服务器架构与工作原理
Servlet是运行在服务器端的Java程序，它的主要职责之一是接收并处理来自客户端（如浏览器）的HTTP请求。当客户端发送一个请求到服务器时，Servlet可以解析请求中的信息，例如请求的URL路径
阅读更多2024-12-14
Vue生命周期钩子函数：深入解析与实践
作为高级Vue前端开发人员，对Vue组件的生命周期钩子函数有着深刻的理解是至关重要的。生命周期钩子函数是指在Vue组件的创建、更新、销毁等过程中，Vue自动调用的一系列方法。通过这些钩子函数，我们可以
阅读更多2024-12-14
安卓开发--使用android studio发布APP
app发布
阅读更多2024-12-14
数据结构与算法学习笔记----拓扑排序
@ author: 明月清了个风。
阅读更多2024-12-14
python 将数据保存到现有的Excel文件的新工作表
out_file = ‘query.xlsx’df1 = pd.DataFrame(out_data)若直接写入：df1.to_excel(out_file, index=False, sheet_n
阅读更多2024-12-14

【mysql】Deadlock found when trying to get lock； 出现原因以及解决方案