Msql-数据库死锁

实验案例

CREATE TABLE `t1_deadlock` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`age` int(11) NOT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE,
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Insert into t1_deadlock(id,name,age,address) values (1,'刘备',18,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (2,'关羽',17,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (3,'张飞',16,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (4,'关羽',16,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (5,'诸葛亮',35,'蜀国');
Insert into t1_deadlock(id,name,age,address) values (6,'曹孟德',32,'魏国');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

案例1

-- Session01
begin;
select * from t1_deadlock where id=1 for update;
update t1_deadlock set name='qqq' where id=5;
commit;
-- Session02
begin;
delete from t1_deadlock where id=5;
delete from t1_deadlock where id=1;
-- 死锁
commit;
1
2
3
4
5
6
7
8
9
10
11

上图紫色表示当前事务持有的锁，红色表示需要等待花获取的锁。我们按照表中是执行顺序，首先事务1给1这条记录加了记录锁，然后事务2给5这条记录加了记录锁，这个时候事务1又想取更新记录5就需要等待事务2提交释放5的记录锁，同样事务2想要删除记录为1的数据也需要等待事务1提交释放1记录的锁。这样事务1等待事务2，事务2等待事务1就造成了死锁。

SHOW ENGINE INNODB STATUS;
1

案例2

CREATE TABLE `t2_deadlock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cnt` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`),
UNIQUE index `idx_cnt` (`cnt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1_deadlock03(id,cnt) values (1,'abc-130-sz');
``
```sql
-- Session01
begin;
delete from t2_deadlock where cnt='abc-130-sz';
insert into t2_deadlock(cnt) values ('abc-130-sz');
commit;
-- Session02
begin;
delete from t2_deadlock where cnt='abc-130-sz';
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

事务1持有了这条记录的写锁，然后插入的时候由于需要先当前读最新的数据，所以需要加读锁，但是写锁是排它的就需要等待写锁释放，但是写锁只有在事务提价的时候才会释放，因此产生了死锁。

如何避免死锁

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。

1. 注意程序的逻辑：根本的原因是程序逻辑的顺序交叠，最常见的是交差更新
2. 保持事务的轻量：越是轻量的事务，占有越少的锁资源，这样发生死锁的几率就越小
3. 提高运行的速度：避免使用子查询，尽量使用主键等等
4. 尽量快提交事务，减少持有锁的时间：越早提交事务，锁就越早释放