MariaDB 10.6如何设置SELECT等待其他事务提交后再执行

架构老林

2026-8-28

问题描述

我当前使用 MariaDB 10.6.5 版本，业务代码如下：

$pdo->query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
$pdo->query("SET autocommit = 0;");

try
{
    $max_id = $pdo->query("SELECT MAX(id) FROM test")->fetchColumn();
    sleep(3);
    $insert_sql = $pdo->prepare("INSERT INTO test(test) VALUES(:test)");
    $insert_sql->execute(['test' => $max_id + 1]);
}
catch (Throwable $e)
{
    $pdo->query("ROLLBACK;");
}

$pdo->query("COMMIT;");

所用的test表包含2个字段：

id：自增属性
test：int类型

需求：两个用户同时执行上述代码时，先发起的事务执行SELECT语句时就锁定test表，后发起的事务执行到SELECT语句时阻塞等待，直到前一个事务执行完成，最终保证表中id列的值始终与test列的值相等。

预期执行流程如下：

两个用户U1和U2同时运行上述代码，其中U1的执行时间比U2早数微秒
U1执行SELECT语句，锁定test表
U1执行INSERT语句
U1执行COMMIT语句提交事务，解锁test表
U2此时才执行SELECT语句，读取U1执行INSERT操作后最新的MAX(id)值
U2执行INSERT语句
U2执行COMMIT语句提交事务

解答

该需求可以实现，无需调整MariaDB服务端配置，只需要修正现有代码的两处问题即可：

普通SELECT在InnoDB引擎下属于快照读，即使设置了SERIALIZABLE隔离级别，全表聚合查询场景下默认也不会加能阻塞其他事务的排他锁，需要在查询语句后加FOR UPDATE后缀，将查询改为当前读，触发锁机制。由于SELECT MAX(id)是全表范围的聚合查询，InnoDB无法精确定位到单行加行锁，会自动对全表加排他Next-Key锁，正好满足查询即锁表、后续事务同查询阻塞的要求。
现有代码的事务逻辑存在漏洞：无论try块内逻辑是否执行成功、是否已经触发回滚，最后都会执行COMMIT，会在异常场景下触发数据库报错，需要将COMMIT逻辑移动到try块内，仅在业务逻辑执行成功后提交。

修正后的代码如下：

$pdo->query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;");
$pdo->query("SET autocommit = 0;");

try
{
    // 加FOR UPDATE触发排他锁，全表聚合场景下自动锁表
    $max_id = $pdo->query("SELECT MAX(id) FROM test FOR UPDATE")->fetchColumn();
    sleep(3);
    $insert_sql = $pdo->prepare("INSERT INTO test(test) VALUES(:test)");
    $insert_sql->execute(['test' => $max_id + 1]);
    // 仅执行成功时提交事务
    $pdo->query("COMMIT;");
}
catch (Throwable $e)
{
    $pdo->query("ROLLBACK;");
    // 可在此处添加自定义错误处理逻辑
}

上述修改后执行逻辑完全符合预期：U1先执行带FOR UPDATE的查询拿到全表排他锁，U2执行相同查询时会直接阻塞，直到U1提交事务释放锁后，U2才会读取到U1插入后的最新MAX(id)值，最终保证id列和test列的值始终一致。
不要使用显式LOCK TABLES语法实现锁表，该语法和InnoDB事务的交互存在隐式提交问题，容易破坏事务原子性，FOR UPDATE触发的锁是InnoDB原生事务级锁，会在事务提交或回滚时自动释放，稳定性更高。

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