MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL死锁检测与解决策略

2023-03-026.9k 阅读

MySQL 死锁概述

在多线程环境下，数据库操作很容易出现死锁问题。死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。MySQL 作为广泛使用的关系型数据库，死锁问题也时有发生。

MySQL 中的死锁通常发生在事务试图获取锁时，当多个事务相互持有对方需要的锁，并且都在等待对方释放锁，就形成了死锁循环。例如，事务 A 持有锁 L1 并试图获取锁 L2，而事务 B 持有锁 L2 并试图获取锁 L1，此时就形成了死锁。

死锁不仅会影响当前事务的执行，还可能导致整个系统性能下降，因为数据库需要花费额外的资源来检测和处理死锁。因此，深入理解 MySQL 死锁的检测与解决策略至关重要。

死锁产生的原因

锁竞争

MySQL 中不同事务对相同资源（如行、表）请求锁时，如果锁的获取顺序不一致，就容易引发死锁。例如，假设有两个事务 T1 和 T2，T1 先获取了行 A 的锁，准备获取行 B 的锁；而 T2 先获取了行 B 的锁，准备获取行 A 的锁，这种情况下就可能发生死锁。

事务并发执行

当多个事务同时运行，并且对相同数据进行读写操作时，死锁的风险显著增加。例如，在一个高并发的电商系统中，多个用户同时下单，每个下单操作可能涉及多个数据库表的更新，若锁机制处理不当，就容易产生死锁。

长时间持有锁

如果一个事务长时间持有锁，而其他事务需要获取相同的锁，就会导致等待时间过长，增加死锁的可能性。比如，一个事务执行复杂的计算逻辑，在持有锁的情况下进行大量非数据库操作，使得其他事务无法及时获取锁。

MySQL 死锁检测机制

InnoDB 引擎的死锁检测

InnoDB 引擎是 MySQL 中常用的存储引擎，它内置了死锁检测机制。InnoDB 使用一种基于等待图（Wait-for Graph，WFG）的算法来检测死锁。等待图是一个有向图，图中的节点表示事务，边表示事务之间的锁等待关系。

当一个事务请求锁时，如果不能立即获取锁，InnoDB 会将该事务添加到等待队列中，并在等待图中创建相应的节点和边。InnoDB 定期检查等待图，若发现图中存在环，就表示发生了死锁。

死锁检测频率

InnoDB 的死锁检测频率并非固定不变，而是根据系统负载动态调整。在低负载情况下，死锁检测频率相对较低；而在高负载时，检测频率会相应提高，以尽快发现并处理死锁。

死锁检测日志

MySQL 会将死锁相关信息记录在错误日志中。通过查看错误日志，可以获取死锁发生的时间、涉及的事务 ID、锁信息等详细内容。例如，错误日志中可能会记录如下信息：

2024-01-01T12:00:00.000000Z 1234 [ERROR] InnoDB: Deadlock found when trying to get lock;
try restarting transaction

从日志中，DBA 可以分析死锁产生的原因，进而采取相应的解决措施。

死锁解决策略

自动回滚

当 InnoDB 检测到死锁时，会自动选择一个事务进行回滚，这个被选中的事务被称为“牺牲者”（Victim）。InnoDB 选择牺牲者的策略通常是基于事务的代价，比如事务已执行的时间、已修改的行数等。一般来说，选择代价较小的事务回滚，以尽量减少对系统的影响。

例如，假设事务 T1 已执行了 1 分钟，修改了 10 行数据；事务 T2 已执行了 30 秒，修改了 5 行数据。在这种情况下，InnoDB 可能会选择 T2 作为牺牲者进行回滚。

重试事务

对于因死锁而回滚的事务，应用程序可以选择重试。在重试之前，建议等待一段随机时间，以避免再次发生死锁。例如，可以使用如下代码实现事务重试：

import mysql.connector
import time
import random

def execute_transaction():
    conn = mysql.connector.connect(user='your_user', password='your_password', host='127.0.0.1', database='your_database')
    cursor = conn.cursor()
    retries = 3
    while retries > 0:
        try:
            conn.start_transaction()
            cursor.execute('UPDATE your_table SET column = value WHERE condition')
            conn.commit()
            break
        except mysql.connector.Error as err:
            if err.errno == 1213: # 死锁错误码
                retries -= 1
                wait_time = random.uniform(0.1, 1) # 随机等待 0.1 到 1 秒
                time.sleep(wait_time)
            else:
                raise err
    conn.close()

优化事务逻辑

通过优化事务逻辑，可以减少死锁的发生。例如，确保事务按相同顺序获取锁，避免交叉获取锁。以下是一个示例，展示了如何通过调整事务逻辑来避免死锁：

-- 错误的事务逻辑，可能导致死锁
START TRANSACTION;
UPDATE table1 SET column1 = value1 WHERE condition1;
UPDATE table2 SET column2 = value2 WHERE condition2;
COMMIT;

-- 正确的事务逻辑，按相同顺序获取锁
START TRANSACTION;
UPDATE table2 SET column2 = value2 WHERE condition2;
UPDATE table1 SET column1 = value1 WHERE condition1;
COMMIT;

调整锁粒度

适当调整锁粒度也可以降低死锁风险。如果锁粒度过大，会导致并发性能下降，但死锁风险相对较低；反之，锁粒度过小，并发性能提高，但死锁风险增加。例如，在某些情况下，可以将表级锁调整为行级锁，以提高并发度，但同时需要更小心地处理死锁问题。

合理设置事务隔离级别

事务隔离级别对死锁有一定影响。不同的隔离级别，锁的使用方式和范围不同。例如，在可重复读（Repeatable Read）隔离级别下，InnoDB 使用的是基于索引的锁，这在一定程度上可以减少死锁的发生。但如果业务允许，将隔离级别降低到读已提交（Read Committed），可以进一步提高并发性能，但可能会增加死锁风险，需要根据实际情况权衡。

死锁模拟与分析

模拟死锁场景

下面通过一个简单的 Python 脚本结合 MySQL 来模拟死锁场景：

import mysql.connector
import threading

def transaction1():
    conn = mysql.connector.connect(user='your_user', password='your_password', host='127.0.0.1', database='your_database')
    cursor = conn.cursor()
    conn.start_transaction()
    cursor.execute('UPDATE table1 SET column1 = column1 + 1 WHERE id = 1')
    time.sleep(1)
    cursor.execute('UPDATE table2 SET column2 = column2 + 1 WHERE id = 1')
    conn.commit()
    conn.close()

def transaction2():
    conn = mysql.connector.connect(user='your_user', password='your_password', host='127.0.0.1', database='your_database')
    cursor = conn.cursor()
    conn.start_transaction()
    cursor.execute('UPDATE table2 SET column2 = column2 + 1 WHERE id = 1')
    time.sleep(1)
    cursor.execute('UPDATE table1 SET column1 = column1 + 1 WHERE id = 1')
    conn.commit()
    conn.close()

t1 = threading.Thread(target=transaction1)
t2 = threading.Thread(target=transaction2)

t1.start()
t2.start()

t1.join()
t2.join()

在上述代码中，transaction1 和 transaction2 两个事务交叉获取 table1 和 table2 的锁，并且通过 time.sleep 模拟事务执行时间，从而增加死锁发生的概率。

分析死锁日志

当死锁发生后，查看 MySQL 错误日志。日志中会包含详细的死锁信息，如事务 ID、锁等待关系等。例如：

2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: Deadlock found when trying to get lock;
try restarting transaction
2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: *** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 123, OS thread handle 1234567890, query id 123456 127.0.0.1 your_user updating
UPDATE table1 SET column1 = column1 + 1 WHERE id = 1
2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 3 n bits 72 index `PRIMARY` of table `your_database`.`table2` trx id 12345 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000003031; asc    01;;
 2: len 7; hex 42000001900110; asc B       ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 61; asc a;;

2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: *** (2) TRANSACTION:
TRANSACTION 67890, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 456, OS thread handle 9876543210, query id 789012 127.0.0.1 your_user updating
UPDATE table2 SET column2 = column2 + 1 WHERE id = 1
2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 131 page no 3 n bits 72 index `PRIMARY` of table `your_database`.`table2` trx id 67890 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000003031; asc    01;;
 2: len 7; hex 42000001900110; asc B       ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 61; asc a;;

2024-01-01T12:30:00.000000Z 5678 [ERROR] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 2 n bits 72 index `PRIMARY` of table `your_database`.`table1` trx id 67890 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000003031; asc    01;;
 2: len 7; hex 42000001900110; asc B       ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 61; asc a;;

从日志中可以清晰地看到两个事务的锁等待关系，事务 12345 持有 table1 的锁并等待 table2 的锁，事务 67890 持有 table2 的锁并等待 table1 的锁，形成了死锁。

基于分析结果的改进

根据死锁日志分析结果，可以对事务逻辑进行改进。例如，统一事务获取锁的顺序，将上述模拟代码修改如下：

import mysql.connector
import threading

def transaction1():
    conn = mysql.connector.connect(user='your_user', password='your_password', host='127.0.0.1', database='your_database')
    cursor = conn.cursor()
    conn.start_transaction()
    cursor.execute('UPDATE table1 SET column1 = column1 + 1 WHERE id = 1')
    cursor.execute('UPDATE table2 SET column2 = column2 + 1 WHERE id = 1')
    conn.commit()
    conn.close()

def transaction2():
    conn = mysql.connector.connect(user='your_user', password='your_password', host='127.0.0.1', database='your_database')
    cursor = conn.cursor()
    conn.start_transaction()
    cursor.execute('UPDATE table1 SET column1 = column1 + 1 WHERE id = 1')
    cursor.execute('UPDATE table2 SET column2 = column2 + 1 WHERE id = 1')
    conn.commit()
    conn.close()

t1 = threading.Thread(target=transaction1)
t2 = threading.Thread(target=transaction2)

t1.start()
t2.start()

t1.join()
t2.join()

通过这种方式，两个事务按照相同顺序获取锁，大大降低了死锁发生的可能性。

死锁预防措施

合理设计数据库架构

在数据库设计阶段，合理设计表结构和索引，可以减少锁竞争，从而预防死锁。例如，避免在高并发操作的表上建立过多的索引，因为过多的索引会增加锁的数量和范围。

控制事务大小

尽量将大事务拆分成多个小事务，减少单个事务持有锁的时间和范围。例如，在一个复杂的业务操作中，如果可以将其拆分为几个独立的小操作，每个小操作作为一个事务执行，就可以降低死锁风险。

定期优化数据库

定期对数据库进行优化，如清理无用数据、重建索引等。这样可以提高数据库性能，减少因数据库性能问题导致的锁等待时间过长，进而降低死锁发生的概率。

监控与预警

建立完善的监控系统，实时监控数据库的锁争用情况、事务执行时间等关键指标。当指标超出正常范围时，及时发出预警，以便 DBA 能够提前采取措施，预防死锁的发生。例如，可以使用 MySQL 的 SHOW ENGINE INNODB STATUS 命令获取 InnoDB 引擎的状态信息，监控锁等待情况。

总结

MySQL 死锁是多线程数据库环境中常见的问题，严重影响系统性能和稳定性。通过深入理解死锁产生的原因、检测机制以及各种解决和预防策略，可以有效地应对死锁问题。在实际应用中，需要结合业务场景，综合运用优化事务逻辑、调整锁粒度、合理设置事务隔离级别等方法，降低死锁发生的概率，确保数据库系统的高效运行。同时，通过模拟死锁场景和分析死锁日志，不断优化系统，进一步提高系统的健壮性。