多租户表中生成发票号时遭遇死锁问题的优化后咨询

问题背景 #

我在一个多租户的发票/订单应用里遇到了棘手的死锁问题：需要为每个租户生成下一个连续的发票号，然后保存对应的发票记录。最开始我用了MySQL的FOR UPDATE行锁，并且把所有逻辑放在事务和try...catch块里，但当用户同时创建大量定时任务（比如20个cron任务在同一时间触发）时，就会频繁出现死锁错误，导致大部分发票生成失败。

最初的实现（简化版） #

最初的代码逻辑大致是这样的：

1. 开启读写事务
2. 先插入一条空白的发票记录
3. 获取这条记录的ID
4. 用FOR UPDATE锁定该租户下所有收入类型的记录
5. 查询该租户的最大发票号，加1得到新的发票号
6. 用新号更新刚才插入的空白记录
7. 提交事务，出错则回滚

但运行时会频繁触发这个错误：

Deadlock found when trying to get lock; try restarting transaction
对应代码里的“couldn't edit the record”分支

这里要说明下，我们是多租户环境，发票号是按租户独立自增的，示例数据如下：

优化后的实现 #

经过一番调研后，我对代码做了调整，主要优化点有三个：

• 增加重试机制：最多重试10次，每次失败后随机等待1-5秒再重新执行事务
• 调整事务的提交/回滚逻辑：不再只依赖try...catch捕获异常，只要任何一步查询出错就主动回滚
• 用循环包裹整个事务流程，直到成功执行或者达到最大重试次数

优化后的完整代码如下：

$cancel = 0;
$retries = 0;
while ($retries<10)
{
    mysqli_begin_transaction($linksgweb, MYSQLI_TRANS_START_READ_WRITE);
    try {
        $query = "insert into mov (IDTENANT,MOV) values ($tenant,'INCOME')";
        if (mysqli_query($linksgweb, $query)) {
            $line = mysqli_insert_id($linksgweb);
            
            $query2 = "select * from mov where IDTENANT = $tenant and MOV='INCOME' FOR UPDATE";
            if (mysqli_query($linksgweb, $query2)) {
                
                $query3 = "select @A:=MAX(NUMBER)+1 from mov where IDTENANT = $tenant and MOV='INCOME'";
                $result = mysqli_query($linksgweb, $query3);
                $nrows = mysqli_num_rows($result);
                
                if (($nrows == 0) || ($row = mysqli_fetch_array($result)))
                {
                    $number = "";
                    if ($nrows != 0)
                        $number = $row[0];
                    if ($number == "")
                        $number = 1;
                        
                    $query4 = "update mov SET NUMBER = " . $number . " where IDTENANT = $tenant and ID = $line";
                    if (mysqli_query($linksgweb, $query4))
                        $cancel = 0;
                    else
                        $cancel = 1;
                }
                else
                    $cancel = 1;
            }
            else
                $cancel = 1;
        }
        else
            $cancel = 1;
            
        if ($cancel==0)
            mysqli_commit($linksgweb);
        else
            mysqli_rollback($linksgweb);
    }
    catch (Exception $e)
    {
        mysqli_rollback($linksgweb);
        $cancel = 1;
    }
    
    if ($cancel==1)
    {
        $retries++;
        sleep(rand(1, 5));
    }
    else
        $retries = 100;  //Force to exit the while loop
}

测试结果 #

目前测试下来效果超出预期：

• 20个定时任务同时执行，所有发票都成功生成，没有死锁错误
• 做了压力测试：60秒内连续调用100次接口，也没有出现死锁或失败的情况

我的疑问 #

虽然现在测试没问题，但我还是有点心里没底，想请教各位大佬：

• 这个优化后的方案有没有什么潜在的隐患？
• 有没有其他需要注意的地方或者可以进一步优化的点？

备注：内容来源于stack exchange，提问作者santycg