YSMull
<-- home

项目中遇到的锁与数据一致性问题

背景

项目表: project(id, name)

报告表: report(id, project_id, name)

有两个操作:

  1. 操作一:删除项目 A (id = 1)
    delete from project where id = 1;
    delete from report where project_id = 1;
    
  2. 操作二:给项目 A 新增一个报告
    insert into report(`project_id`, `name`) values(1, '报告1');
    

问题

如何保证两个操作并行进行之后的数据一致性,即:操作一和操作二都结束后,数据库里没有项目 ID = 1 的报告。

解决方案

方案一: 外键约束

给 report 表的 project_id 字段加外键。
在有外键之后,在插入 report 的时候,会尝试获取 project 表的相关记录的 X 锁。

  1. 如果此时可以获取锁,那么此时操作一一定还没有执行删除项目操作,报告会添加成功。随后操作一在删除项目的时候,会把该报告删除。
  2. 如果此时不能获取锁,说明操作一正在删除该插入报告所在的项目。等操作一的事务提交后,操作二会插入失败,报外键错误。

注意,此时操作一所在的事务,SQL一定得按如下顺序执行,先删除依赖,否则报外键错误

delete from report where project_id = 1;
delete from project where id = 1;

方案二: 一致性锁定读

操作二使用如下逻辑操作,在插入报告之前,先判断是否存在该项目,查询时给项目加上 S 锁(也可以 for update 加上 X 锁)。我们用存储过程描述这个过程:

DROP PROCEDURE IF EXISTS insert_report;

CREATE procedure insert_report(IN pid int, IN title varchar(255))
BEGIN
    start transaction ;
    SELECT id into @project_id FROM project WHERE id = 1 lock in share mode;
    if @project_id is not null
    then
        insert into new_report(`project_id`, `name`) value (pid, title);
    end if;
    commit;
END;

call insert_report(1, '报告1');

事务一的两句SQL语句还可以交换顺序,两个事务的操作排列组合之后非常多,我们怎么知道在任何执行顺序下,这样可以解决数据一致性问题呢?

方案二的证明

证明:
当事务二执行 select id from project where id = 1 lock in share mode 返回结果后,到事务二commit之前的这段时间之内

事务一要么「已经删除」了 project 表中 id = 1 的记录,要么还在「等待删除」 project 表中 id = 1 的记录。

  1. 事务一「已经删除」 project 表中 id 为 1 的记录
    那么事务二不会执行 insert 语句添加 project_id 为 1 的 记录,最终保持了数据一致。

  2. 事务一在「等待删除」project 表中 id 为 1 的记录,那么事务二接下来将插入 project_id=1 的 report

    • 2.1 如果事务一已经删除了 project_id=1 的report
      那么事务二无法插入 project_id=1 的 report ,导致死锁,触发 MySQL 回滚。
    • 2.2 如果事务一还没有删除 project_id=1 的report
      那么事务二可以插入 project_id=1 的 report ,并且可以commit,事务二comimt后,会释放 project 表中 id = 1 的记录的 S 锁,从而事务一可以拿到 project 中 id = 1 的记录的 X 锁,删除 project 表中 id= 1 的记录,然后删除 report 表中所有 project_id = 1 的记录。

    无论是 2.1 还是 2.2,最终数据库中没有 id=1 的 project,也没有 project_id=1 的 report,保持了一致性。

方案二死锁举例

上面的证明提到了可能会带来死锁,下面给出死锁的情况。
考虑如下的操作序列

事务1 事务2
delete from report where project_id = 1; -- 给 report 表加了 X 锁  
  SELECT id into @project_id FROM project WHERE id = 1 lock in share mode; – 给 project 表加了 S 锁
delete from project where id = 1; – 尝试获取获取 project 表的 X 锁,阻塞  
  insert into report(project_id, name) value (1, ‘报告1’); –尝试获取 report 表的 X 锁,阻塞

这样就导致了死锁。还好 MySQL 会自动回滚其中一个事务,算是保证了数据一致性。