mysql 批量更新造成死锁

基础概念

MySQL中的死锁是指两个或多个事务互相等待对方释放资源，导致所有事务都无法继续执行的情况。批量更新操作由于涉及多个数据行的修改，如果处理不当，很容易引发死锁。

类型

MySQL中的死锁主要有两种类型：

循环等待：事务A等待事务B释放资源，事务B又等待事务C释放资源，依此类推，形成一个循环等待链。
持有并等待：事务已经持有一个或多个资源，但又请求其他事务持有的资源，导致无法继续执行。

应用场景

批量更新通常用于需要同时更新多条记录的场景，例如批量修改用户状态、批量调整价格等。

问题原因

批量更新造成死锁的原因通常包括：

事务隔离级别：较高的隔离级别（如可重复读）可能导致更多的锁冲突。
更新顺序：不同的更新顺序可能导致不同的锁冲突模式。
锁粒度：细粒度的锁可能导致更多的锁冲突。
并发度：高并发环境下更容易发生死锁。

解决方法

优化事务隔离级别：根据业务需求选择合适的隔离级别，避免不必要的锁冲突。
统一更新顺序：尽量保证所有事务按照相同的顺序更新数据，减少锁冲突的可能性。
减少锁粒度：使用更粗粒度的锁，例如表级锁，减少锁冲突。
设置超时时间：为事务设置合理的超时时间，避免长时间占用资源。
使用乐观锁：通过版本号或时间戳等方式实现乐观锁，减少锁的使用。

示例代码

以下是一个简单的示例，展示如何通过统一更新顺序来避免死锁：

-- 假设有两个表：users 和 orders
-- users 表结构：id, status
-- orders 表结构：id, user_id, amount

-- 事务1
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id IN (1, 2, 3);
UPDATE orders SET amount = amount * 1.1 WHERE user_id IN (1, 2, 3);
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE users SET status = 'inactive' WHERE id IN (3, 4, 5);
UPDATE orders SET amount = amount * 0.9 WHERE user_id IN (3, 4, 5);
COMMIT;

为了避免死锁，可以统一更新顺序：

-- 事务1
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id IN (1, 2, 3);
UPDATE orders SET amount = amount * 1.1 WHERE user_id IN (1, 2, 3);
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE users SET status = 'inactive' WHERE id IN (3, 4, 5);
UPDATE orders SET amount = amount * 0.9 WHERE user_id IN (3, 4, 5);
COMMIT;