一次 Dead Lock 事件的处理

发表于

表结构

1
2
3
4
5
6
7
8
CREATE TABLE `group_works` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `group` varchar(50) NOT NULL,
  `work` varchar(50) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_group_work` (`group`,`work`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

代码逻辑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
tran := extension.MySQL.Begin()

err := tran.Exec("DELETE FROM group_works WHERE group= ?", group).Error
    if err != nil {
        tran.Rollback()
        return err
    }
}

for _, work := range works {
    err := tran.Create(&GroupWork{
        Group:      group,
        Work:       work,
        CreatedAt:  time.Now(),
    }).Error
    if err != nil {
        tran.Rollback()
        return err
    }
}

return tran.Commit().Error

死锁步骤

打开两个 mysql cli ， 分别开启事务：

transation A:

1
2
3
4
5
begin;    # step 1

DELETE FROM group_works WHERE `group` = '1111'; # step 3

INSERT INTO `group_works` (`group`,`work`,`createdAt`) VALUES ('1111','test','2019-02-27 16:31:13.991669');  # step 5

transation B:

1
2
3
4
5
begin; # step 2

DELETE FROM group_works WHERE `group` = '1111';  # step 4

INSERT INTO `group_works` (`group`,`work`,`createdAt`) VALUES ('1111','test','2019-02-27 16:31:13.991669');   # step 6

image

测试发现当 没有 group = ‘1111’ 的记录时 去做 DELETE, 会发生死锁， 如果存在记录，反而不会死锁。

image

解决方案

在做 DELETE 之前先判断是否有记录，当存在记录的时候才执行 DELETE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
tran := extension.MySQL.Begin()

var total int
tran.Table("group_works").Where("`group` = ?", group).Count(&total)
if total > 0 { // to avoid dead lock when delete no record found, it will add gap lock
    err := tran.Exec("DELETE FROM group_works WHERE `group` = ?", group).Error
    if err != nil {
        tran.Rollback()
      return err
    }
}

for _, work := range works {
    err := tran.Create(&GroupWork{
        Group:      group,
        Work:       work,
        CreatedAt:  time.Now(),
    }).Error
    if err != nil {
        tran.Rollback()
        return err
    }
}

return tran.Commit().Error

参考

何登成的技术博客