如何通过PHP安全同步更新两个MySQL数据库中的关联表

场景背景 #

我有一个Laravel应用A，对应MySQL数据库db1及表p1；另有一个独立PHP应用B，对应MySQL数据库db2及等价表p2。两张表为一对一关联（对应同一实体），部分字段独有、部分字段共用。现需从应用B出发，找到安全完善的方案同时更新db1.p1与db2.p2，针对以下问题逐一解答：

1. 主问题：跨库事务更新是否会锁定记录？需注意哪些风险？ #

• 记录锁定情况：会锁定。执行UPDATE语句时，MySQL会对匹配的db1.p1和db2.p2记录加排他锁（X锁），直到事务提交或回滚才释放。InnoDB引擎下是行级锁，仅锁定命中行；MyISAM则是表级锁，会锁定整个表。
• 核心风险：
  • 跨库一致性失效：MySQL原生不支持跨库事务（XA事务实现复杂且性能损耗大），你提供的SQL看似单事务，但本质是同一连接操作两个库，若其中一个库更新失败，另一个库的更新无法自动回滚，直接导致数据不一致。
  • 死锁概率提升：如果应用A同时操作两张表，且锁的获取顺序与应用B相反，极易触发死锁。
  • 锁等待超时：事务持有锁时间过长时，其他操作会因等待锁超时失败，影响系统可用性。
  • 权限安全隐患：应用B的数据库账号需要同时拥有db1和db2的更新权限，权限过大可能带来数据泄露或误操作风险。

2. 额外场景：事务提交结果判断 #

这个场景下提交事务会报错，原因如下：
步骤2.2更新db1.p1的fk_id=23时，InnoDB会立即检查f1表是否存在id=23的记录，此时检查通过；但步骤2.3中应用A删除了该记录，事务提交时InnoDB会再次执行外键约束校验（确保提交时数据仍符合约束），发现f1中无对应记录，触发外键约束错误，事务回滚。

3. 补充疑问1：应用A是否适用同一方案？是否应仅单应用执行更新？ #

• 是否适用同一方案：不适用。应用A是Laravel应用，通常依赖Eloquent ORM操作数据库，直接使用原生跨库SQL会破坏ORM封装性，且Laravel默认事务管理仅支持单库，跨库事务需额外处理。
• 是否仅单应用执行更新：建议指定单一应用（如应用B）作为跨库更新的唯一入口，原因：
  • 避免多应用并发操作带来的冲突、死锁问题；
  • 统一更新逻辑，减少重复代码与维护成本；
  • 便于监控与排查问题，所有跨库操作集中管理。
    若必须让应用A支持更新，应通过接口调用方式，让应用A请求应用B的更新接口，由应用B统一执行跨库更新逻辑。

4. 补充疑问2：事务隔离级别选择与配置 #

隔离级别选择

推荐使用REPEATABLE READ（可重复读），这是MySQL InnoDB的默认隔离级别：

• 避免脏读、不可重复读；
• 通过间隙锁解决幻读问题；
• 在一致性与并发性能间达到较好平衡。
  仅在金融等对一致性要求极高的场景，才考虑SERIALIZABLE（串行化），但该级别会严重降低并发性能，不推荐常规场景使用。

配置与代码示例

方式1：MySQL全局配置

修改my.cnf或my.ini配置文件：

[mysqld]
transaction-isolation = REPEATABLE-READ

修改后重启MySQL服务生效。

方式2：PHP应用B中动态设置（PDO示例）

// 初始化数据库连接
$pdo = new PDO('mysql:host=localhost;dbname=db2;charset=utf8mb4', 'username', 'password');

// 设置会话级隔离级别为可重复读
$pdo->exec('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');

// 开启事务
$pdo->beginTransaction();

try {
    // 执行跨库更新
    $sql = "UPDATE db1.p1 pp1 inner join db2.p2 pp2 on pp1.iddb2=pp2.id
            SET 
               pp1.fk_id=23,
               pp1.commoncol='a',
               pp2.commoncol='a',
               pp2.extracol='b'
            WHERE pp2.id=123;";
    $pdo->exec($sql);
    
    // 提交事务
    $pdo->commit();
} catch (PDOException $e) {
    // 回滚事务
    $pdo->rollBack();
    throw $e;
}

方式3：Laravel应用A中配置

修改config/database.php中MySQL连接的隔离级别：

'mysql' => [
    // ... 其他配置项
    'isolation_level' => 'REPEATABLE READ',
],

Eloquent事务使用示例：

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    DB::statement("UPDATE db1.p1 pp1 inner join db2.p2 pp2 on pp1.iddb2=pp2.id
            SET 
               pp1.fk_id=23,
               pp1.commoncol='a',
               pp2.commoncol='a',
               pp2.extracol='b'
            WHERE pp2.id=123;");
});

内容的提问来源于stack exchange，提问作者tvv3