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

• 背景
• 问题
• 解决方案
  • 方案一: 外键约束
  • 方案二: 一致性锁定读
    • 方案二的证明
    • 方案二死锁举例

背景

项目表: 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');
   

问题

一致性的定义：如何保证两个操作并行进行之后

1. 数据库里没有项目 id=1 的 project
2. 数据库里也没有 project_id=1 的 report。

解决方案

方案一: 外键约束

给 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，保持了一致性。

方案二死锁举例

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

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