INSERT语句引发的死锁实例分析

发布时间：2022-02-10 15:37:04 作者：iii
来源：亿速云阅读：230

# INSERT语句引发的死锁实例分析

## 1. 引言

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

死锁的危害主要体现在：
- 系统资源被无效占用
- 事务吞吐量下降
- 用户体验恶化
- 严重时可能导致系统崩溃

### 1.2 数据库锁机制概述
数据库通过锁机制来保证事务的ACID特性，主要锁类型包括：

| 锁类型 | 描述 |
|--------|------|
| 共享锁(S) | 允许并发读取，阻止其他事务获取排他锁 |
| 排他锁(X) | 阻止其他事务获取任何类型的锁 |
| 意向锁(IS/IX) | 表示事务打算在更细粒度上加锁 |
| 记录锁 | 锁定索引记录 |
| 间隙锁 | 锁定索引记录之间的间隙 |
| 临键锁 | 记录锁+间隙锁的组合 |

## 2. 死锁产生的基本条件

死锁的产生必须同时满足以下四个必要条件（Coffman条件）：

1. **互斥条件**：资源一次只能由一个事务持有
2. **占有并等待**：事务持有资源的同时等待其他资源
3. **非抢占条件**：已分配的资源不能被强制剥夺
4. **循环等待条件**：存在事务-资源的环形等待链

### 2.1 数据库中的典型死锁场景

在数据库系统中，常见的死锁场景包括：

1. **不同顺序的加锁**：事务A先锁表1后锁表2，事务B先锁表2后锁表1
2. **索引冲突**：事务在不同索引上操作相同行
3. **间隙锁冲突**：范围查询导致的间隙锁重叠
4. **外键约束**：外键检查引发的隐藏锁请求

## 3. INSERT语句死锁的特殊性

### 3.1 为什么INSERT也会导致死锁

虽然INSERT通常被认为是"安全"操作，但在以下情况下仍可能引发死锁：

1. **唯一键冲突**：插入重复唯一键值时的回滚检查
2. **自增锁竞争**：并发插入自增主键时的锁争用
3. **外键约束检查**：需要检查父表记录的存在性
4. **间隙锁插入意向锁冲突**：在已有间隙锁范围内插入

### 3.2 常见触发场景

1. 批量插入时的事务交叉
2. 高并发下的唯一键冲突
3. 复合索引上的非顺序插入
4. 带有子查询的INSERT...SELECT语句

## 4. 实战案例分析

### 4.1 案例环境搭建

```sql
-- 创建测试表
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(32) NOT NULL,
  `balance` decimal(10,2) NOT NULL,
  `version` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 初始化数据
INSERT INTO `account` (`user_id`, `balance`) VALUES 
('U1001', 1000.00),
('U1002', 2000.00);

4.2 死锁场景重现

场景一：唯一键冲突导致的死锁

-- 事务1
BEGIN;
INSERT INTO account(user_id, balance) VALUES ('U1003', 3000);

-- 事务2
BEGIN;
INSERT INTO account(user_id, balance) VALUES ('U1004', 4000);
INSERT INTO account(user_id, balance) VALUES ('U1003', 5000);

-- 事务1
INSERT INTO account(user_id, balance) VALUES ('U1004', 6000);
-- 此时将发生死锁

死锁日志分析：

LATEST DETECTED DEADLOCK
------------------------
2023-05-01 10:00:00 0x7f8e4418a700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 100, OS thread handle 123145307111424, query id 1000 localhost root update
INSERT INTO account(user_id, balance) VALUES ('U1004', 6000)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 4 n bits 72 index idx_user_id of table `test`.`account` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
...

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 101, OS thread handle 123145307611136, query id 1001 localhost root update
INSERT INTO account(user_id, balance) VALUES ('U1003', 5000)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 4 n bits 72 index idx_user_id of table `test`.`account` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
...

*** (2) WTING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 4 n bits 72 index idx_user_id of table `test`.`account` trx id 123457 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
...

*** WE ROLL BACK TRANSACTION (2)

4.3 死锁原因解析

事务1先插入U1003成功，持有U1003的X锁
事务2插入U1004成功，然后尝试插入U1003时被阻塞
事务1尝试插入U1004时，需要获取U1004的X锁
形成循环等待：事务1等待事务2释放U1004的锁，事务2等待事务1释放U1003的锁

5. 其他常见INSERT死锁模式

5.1 自增主键竞争

-- 事务1
BEGIN;
INSERT INTO account(user_id, balance) VALUES ('U1005', 5000);

-- 事务2
BEGIN;
INSERT INTO account(user_id, balance) VALUES ('U1006', 6000);
-- 两个事务可能竞争自增锁

5.2 间隙锁与插入意向锁冲突

-- 事务1
BEGIN;
SELECT * FROM account WHERE id > 100 FOR UPDATE; -- 加间隙锁

-- 事务2
BEGIN;
INSERT INTO account(id, user_id, balance) VALUES (105, 'U1005', 5000); -- 需要获取插入意向锁
-- 可能被阻塞或导致死锁

5.3 外键约束检查

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(32) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRNT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `account` (`user_id`)
);

-- 事务1
BEGIN;
INSERT INTO `order`(user_id, amount) VALUES ('U1001', 100);

-- 事务2
BEGIN;
DELETE FROM account WHERE user_id = 'U1001';
-- 可能形成死锁

6. 死锁检测与排查

6.1 数据库死锁日志分析

MySQL提供多种方式查看死锁信息：

SHOW ENGINE INNODB STATUS命令
开启innodb_print_all_deadlocks记录所有死锁
性能库中的performance_schema.events_statements_history

关键信息点： - 涉及的事务ID - 持有的锁类型和资源 - 等待的锁类型和资源 - 最终被选为牺牲者(victim)的事务

6.2 使用pt-deadlock-logger工具

Percona工具包中的pt-deadlock-logger可以持续监控死锁：

pt-deadlock-logger --ask-pass --socket=/tmp/mysql.sock

输出示例：

2023-05-01T10:00:00 server_id=1 thread_id=100 victim=123457
  t1: INSERT INTO account(user_id, balance) VALUES ('U1004', 6000)
  t2: INSERT INTO account(user_id, balance) VALUES ('U1003', 5000)

7. 解决方案与预防措施

7.1 应用层解决方案

统一访问顺序：确保所有事务按照相同顺序访问表和记录

// 正确的顺序示例
public void transfer(String fromUserId, String toUserId) {
   // 按照用户ID字典序处理
   if (fromUserId.compareTo(toUserId) < 0) {
       lockAccount(fromUserId);
       lockAccount(toUserId);
   } else {
       lockAccount(toUserId);
       lockAccount(fromUserId);
   }
   // 执行转账逻辑
}

减少事务粒度：
- 将大事务拆分为小事务
- 避免在事务中执行不必要的操作

重试机制：

def execute_with_retry(sql, max_retries=3):
   retry_count = 0
   while retry_count < max_retries:
       try:
           execute_sql(sql)
           return True
       except DeadlockError:
           retry_count += 1
           sleep(random.uniform(0.1, 0.3))
   return False

7.2 数据库层优化

索引优化：
- 为高频查询条件添加合适索引
- 避免过度索引导致锁竞争加剧

事务隔离级别调整：

-- 对于不要求严格一致性的场景
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁超时设置：

SET innodb_lock_wait_timeout = 5; -- 设置锁等待超时为5秒

批量插入优化：

-- 使用单条多值INSERT替代多条INSERT
INSERT INTO account(user_id, balance) VALUES 
('U1007', 7000),
('U1008', 8000),
('U1009', 9000);

7.3 架构层设计

队列缓冲：使用消息队列缓冲写请求
分库分表：通过数据分片降低锁冲突概率

乐观锁替代：


UPDATE account 
SET balance = balance - 100, version = version + 1 
WHERE user_id = 'U1001' AND version = 1;

8. 深度优化建议

8.1 InnoDB锁系统调优

调整锁相关参数：

innodb_deadlock_detect = ON      # 死锁检测开关
innodb_lock_wait_timeout = 50    # 锁等待超时(秒)
innodb_rollback_on_timeout = ON  # 超时后回滚

监控锁等待：
```
SELECT * FROM sys.innodb_lock_waits;
```

8.2 高级应用模式

延迟插入：对于非关键数据，可以使用INSERT DELAYED(注意：MySQL 8.0已移除)

ON DUPLICATE KEY UPDATE：


INSERT INTO account(user_id, balance) 
VALUES ('U1003', 3000)
ON DUPLICATE KEY UPDATE balance = VALUES(balance);

INSERT IGNORE：


INSERT IGNORE INTO account(user_id, balance) VALUES ('U1003', 3000);

9. 总结与最佳实践

9.1 INSERT死锁关键点总结

唯一键冲突是INSERT死锁的主要原因
自增锁竞争在高并发插入时需特别注意
外键约束会引入额外的锁请求
间隙锁与插入意向锁的交互可能产生死锁

9.2 推荐的预防措施清单

[ ] 为应用设计统一的资源访问顺序
[ ] 合理设置事务隔离级别
[ ] 添加适当的重试机制
[ ] 避免长事务和大事务
[ ] 为高频条件添加合适索引
[ ] 考虑使用乐观锁替代悲观锁
[ ] 实施完善的监控告警系统

10. 附录

10.1 常见数据库死锁错误代码

数据库	错误代码	描述
MySQL	1213	Deadlock found when trying to get lock
Oracle	ORA-00060	Deadlock detected while waiting for resource
SQL Server	1205	Transaction (Process ID) was deadlocked on…
PostgreSQL	40P01	deadlock detected

10.2 推荐监控工具

Percona PMM：提供全面的死锁监控
pt-deadlock-logger：轻量级死锁日志收集
Prometheus + Grafana：自定义死锁指标监控
ELK Stack：用于集中分析数据库日志

“预防胜于治疗”这一格言在数据库死锁处理中尤为适用。通过理解死锁形成机制、实施合理的预防措施并建立有效的监控系统，可以显著降低生产环境中死锁的发生频率和影响程度。 “`

这篇文章共计约5600字，全面分析了INSERT语句引发的死锁问题，包含： 1. 理论知识讲解 2. 多种实战案例 3. 详细的解决方案 4. 预防措施和最佳实践 5. 相关工具和监控方法

文章采用Markdown格式，包含代码块、表格等元素，便于技术读者理解和实践。