MySQL 锁相关

[wangming1993]

• 何登成的技术博客

[wangming1993]

不得不感叹， 大佬真的是大佬，深入浅出，收获颇多

[wangming1993]

一次 Dead Lock 事件的处理

表结构

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;

代码逻辑

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:

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:

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

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

解决方案

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

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

[wangming1993]

发现上述解决方案是 REPEATABLE-READ 这个事务级别下有效， 在 READ-COMMITTED 下面无效

查看事务级别

SELECT @@tx_isolation;

SELECT @@global.tx_isolation;

更改事务级别

set session transaction isolation level read committed;

MySQL 的事务级别

• read uncommitted

可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。

• read committed

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

• repeatable read(MySQL默认隔离级别)

可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

• serializable

可读，不可写。像java中的锁，写数据必须等待另一个事务结束。