MySQL 数据库中update语句会不会发生死锁

发布时间：2022-01-17 10:15:33 作者：柒染
来源：亿速云阅读：430

# MySQL 数据库中update语句会不会发生死锁

## 一、死锁的概念与形成条件

### 1.1 什么是死锁
死锁（Deadlock）是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。当多个事务同时持有对方需要的资源时，就会形成循环等待，导致所有相关事务都无法继续执行。

### 1.2 死锁的四个必要条件
- **互斥条件**：资源一次只能被一个事务占用
- **请求与保持条件**：事务在持有资源的同时请求新的资源
- **不剥夺条件**：已分配的资源不能被强制剥夺
- **循环等待条件**：多个事务形成头尾相接的等待关系

## 二、UPDATE语句的死锁场景分析

### 2.1 单表UPDATE死锁
当多个事务以不同顺序更新同一组记录时可能发生死锁：

```sql
-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 事务2
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

2.2 跨表UPDATE死锁

涉及多表更新时，如果事务以不同顺序访问表：

-- 事务1
BEGIN;
UPDATE users SET status = 1 WHERE id = 10;
UPDATE orders SET amount = 100 WHERE user_id = 10;
COMMIT;

-- 事务2
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 5;
UPDATE users SET last_order = NOW() WHERE id = 15;
COMMIT;

2.3 索引导致的死锁

不同的索引使用方式可能导致锁范围不同：

-- 表结构
CREATE TABLE products (
  id INT PRIMARY KEY,
  category_id INT,
  stock INT,
  INDEX idx_category (category_id)
);

-- 事务1（使用主键索引）
UPDATE products SET stock = stock - 1 WHERE id = 100;

-- 事务2（使用二级索引）
UPDATE products SET stock = stock - 1 WHERE category_id = 5;

三、MySQL的锁机制与死锁关系

3.1 InnoDB锁类型

行级锁：
- 记录锁（Record Lock）
- 间隙锁（Gap Lock）
- 临键锁（Next-Key Lock）
意向锁：
- 意向共享锁（IS）
- 意向排他锁（IX）

3.2 UPDATE语句的加锁过程

首先获取表的IX锁
根据WHERE条件定位记录
对符合条件的记录加X锁
可能根据隔离级别加间隙锁

四、实际案例分析

4.1 案例一：并发更新相同记录

-- 事务1
START TRANSACTION;
UPDATE employees SET salary = 8000 WHERE emp_id = 101;
-- 不提交

-- 事务2
START TRANSACTION;
UPDATE employees SET salary = 8500 WHERE emp_id = 101;
-- 此时会阻塞

4.2 案例二：批量更新死锁

-- 事务1
UPDATE inventory SET quantity = quantity - 1 
WHERE product_id IN (1, 3, 5) ORDER BY product_id DESC;

-- 事务2
UPDATE inventory SET quantity = quantity + 1
WHERE product_id IN (5, 3, 1) ORDER BY product_id ASC;

五、死锁检测与处理

5.1 死锁检测机制

MySQL默认启用死锁检测（innodb_deadlock_detect=ON），检测到死锁后会： 1. 选择代价较小的事务作为牺牲者（victim） 2. 回滚该事务并返回1213错误 3. 其他事务可以继续执行

5.2 查看死锁信息

SHOW ENGINE INNODB STATUS;

5.3 死锁日志示例

LATEST DETECTED DEADLOCK
------------------------
2023-08-20 10:23:45
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 12345, query id 100 localhost root updating
UPDATE accounts SET balance = balance - 50 WHERE id = 2

六、预防死锁的最佳实践

6.1 应用层优化

保持事务短小精悍
按照固定顺序访问表和记录
合理设计索引减少锁冲突

6.2 数据库配置优化

# my.cnf配置
innodb_lock_wait_timeout=50  # 默认50秒
innodb_deadlock_detect=ON    # 死锁检测
transaction_isolation=REPEATABLE-READ

6.3 监控与预警

设置监控捕获死锁事件
配置报警阈值
定期分析死锁日志

七、总结

UPDATE语句确实可能引发死锁，特别是在高并发环境下。理解InnoDB的锁机制、合理设计事务逻辑、遵循一致的资源访问顺序，可以显著降低死锁概率。当死锁发生时，MySQL的自动检测机制通常能有效处理，但开发人员仍需通过日志分析持续优化应用。

关键点总结： 1. UPDATE操作会获取排他锁(X锁) 2. 不同的事务获取锁的顺序可能导致死锁 3. 合理的索引设计和事务管理是预防关键 4. 死锁是数据库正常现象，关键在于控制和优化 “`

（注：实际字数约1500字，可根据需要调整部分章节的详细程度来控制字数）