mysql死锁演示

小咪咪 2023-05-22 06:35 8阅读 0赞

## 背景： ##

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

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

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;

执行后的结果，出现了死锁的提示

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70][]

### 第三步：查询死锁日志 ###

SHOW ENGINE INNODB STATUS

事务1日志

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1][]

事务2

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 2][]

### 第四步：相应的知识点 ###

行锁：必须有索引才能实现，否则会自动锁全表
    排它锁：写锁，当一个事务对几个上写锁时，不允许其他事务。
    共享锁：读锁，当一个事务对某几行上读锁，允许其他事务对这几行进行读操作，但不允许进行写操作
    
    select 默认不加锁
    update delete insert 默认加排它锁
    select ... for update 加排它锁
    select ... lock in share mode 加共享锁

### 第五步：分析 ###

根据日志及相应知识点分析可得出

事务2持有了事务1对应id的共享锁，等待加排它锁

事务1等待事务2持有的共享锁释放，它才可以加排它锁

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70]: /images/20230521/5f80d287c25e42d8b32fc6dcd995daaa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 1]: /images/20230521/3d2f703f8a4943e1bfd4ceb50c9ac915.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA3NzIyMzA_size_16_color_FFFFFF_t_70 2]: /images/20230521/9a3273b30d2f4b0ca50328a80702dafe.png