mysql死锁演示

背景：

线上日志突然爆了有数据库死锁的日志。

通过以下语句查询数据库死锁的日志

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持有的共享锁释放，它才可以加排它锁

知识有限，如果分析有误，欢迎评论留言。