mySQL 锁定用于简单的多重选择

Question

我在存储过程中有以下简单的两次选择序列:

SELECT * FROM mytable WHERE <conditions>;
SELECT id FROM mytable ORDER BY id DESC LIMIT 1;

其中id是mytable的主键。

总体目的是从mytable中选择一些行(包括最新的行 - 即具有最高id值的行)，然后在第二次选择时，获取同一个表中最后/最新行的id。

问题:如何确保在第一个和第二个选择之间不会插入新记录？

我打算使用:

TRANSACTION
select <1..> FOR UPDATE;
select <2..>;
COMMIT

但这总是有效吗？有更好的方法吗？

或者可以做一些不同的事情来实现从第一个选择中获取最高 id 值而不进行第二个选择的目标？

Answer 1

当您没有更改隔离级别时，您将使用默认的REPEATABLE READ，它已经解决了这个问题。

要检查您正在使用哪个隔离级别，您可以执行以下查询:

SELECT @@global.tx_isolation, @@session.tx_isolation;

全局值是新创建 session 的默认值。 session值当然是你当前使用的值。

您可以使用以下命令为您的 session 显式设置它

SET SESSION tx_isolation='REPEATABLE-READ';

使用REPEATABLE-READ，您可以简单地执行以下操作:

START TRANSACTION;
SELECT * FROM mytable WHERE <conditions>;
SELECT id FROM mytable ORDER BY id DESC LIMIT 1;
COMMIT;

当另一个 session 在您的两次选择期间将行插入表中时，该行将不会显示在您的事务中。

我在我的答案 here 中发布了有关隔离级别的更多详细信息.

如果您想将两个查询合并为一个，您可以这样做:

SELECT m.*, s.maxid 
FROM mytable m 
CROSS JOIN (SELECT MAX(id) AS maxid FROM mytable) s 
WHERE <conditions>;