背景:
线上日志突然爆了有数据库死锁的日志。
通过以下语句查询数据库死锁的日志
SHOW ENGINE INNODB STATUS
通过 日志分析,看到了两条update语句并且是里面有子查询。还有两个表的更新顺序问题。
解决方案是:加了分布式锁,让两个update语句放到一起执行,然后再调整两个update语句里面的子查询。
得出的结论是:不要在update语句里面写子查询。
以下模拟以下线上的死锁例子:
第一步:新建表
CREATE TABLE `user` (
`id` bigint(255) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`uid` varchar(255) DEFAULT NULL,
`school` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf32;
插入一条语句
INSERT INTO `mydb`.`user` (`id`, `name`, `uid`, `school`, `age`) VALUES ('1', 'name88888999', '55', '5', '5');
第二步:打开两个session ,更新同一条记录,模拟并发,这里做了一下sleep操作,方便模拟
BEGIN;
update user set name="session1"
where id = (
select temp.id from (
select id,sleep(5) from user
where id=1
) temp
);
COMMIT;
BEGIN;
update user set name="sssion2"
where id = (
select temp.id from (
select id,sleep(5) from user
where id=1
) temp
);
COMMIT;
执行后的结果,出现了死锁的提示
第三步:查询死锁日志
SHOW ENGINE INNODB STATUS
事务1日志
事务2
第四步:相应的知识点
行锁:必须有索引才能实现,否则会自动锁全表
排它锁:写锁,当一个事务对几个上写锁时,不允许其他事务。
共享锁:读锁,当一个事务对某几行上读锁,允许其他事务对这几行进行读操作,但不允许进行写操作
select 默认不加锁
update delete insert 默认加排它锁
select ... for update 加排它锁
select ... lock in share mode 加共享锁
第五步:分析
根据日志及相应知识点分析可得出
事务2持有了事务1对应id的共享锁,等待加排它锁
事务1等待事务2持有的共享锁释放,它才可以加排它锁
知识有限,如果分析有误,欢迎评论留言。