sql - 带有显式锁和并发事务的错误 PostgreSQL 查询结果

Question

在为 PostgreSQL 编写一些 SQL 查询时，我发现了一些不寻常的行为，我觉得这有点令人不安。

假设我们有下表“测试”:

+----+-------+---------------------+
| id | value |     created_at      |
+----+-------+---------------------+
|  1 | A     | 2014-01-01 00:00:00 |
|  2 | A     | 2014-01-02 00:00:00 |
|  3 | B     | 2014-01-03 00:00:00 |
|  4 | B     | 2014-01-04 00:00:00 |
|  5 | A     | 2014-01-05 00:00:00 |
|  6 | B     | 2014-01-06 00:00:00 |
|  7 | A     | 2014-01-07 00:00:00 |
|  8 | B     | 2014-01-08 00:00:00 |
+----+-------+---------------------+

有两个事务，A 和 B，并行运行。

A: begin;           /* Begin transaction A */
B: begin;           /* Begin transaction B */
A: select * from test where id = 1 for update; /* Lock one row */
B: select * from test where value = 'B' order by created_at limit 3 for update; /* This query returns immediately since it does not need to return row with id=1 */
B: select * from test where value = 'A' order by created_at limit 3 for update; /* This query blocks because row id=1 is locked by transaction A */
A: update test set created_at = '2014-01-09 00:00:00' where id = 1; /* Modify the locked row */
A: commit;

事务A一提交并释放id=1的行，事务B的阻塞查询返回如下结果:

+----+-------+---------------------+
| id | value |     created_at      |
+----+-------+---------------------+
|  1 | A     | 2014-01-09 00:00:00 |
|  2 | A     | 2014-01-02 00:00:00 |
|  5 | A     | 2014-01-05 00:00:00 |
+----+-------+---------------------+

这些行肯定不是按“created_at”排序的，id=1 的行甚至不应该在返回的行中。事务 A 和 B 同时运行的事实导致事务 B 产生了错误的结果，如果事务一个接一个地执行就不会发生这种情况。这似乎违反了事务隔离。

这是一个错误吗？

如果这不是错误并且这些结果是预期的，那么就数据库返回的结果的可靠性而言，这意味着什么？如果我有一个高度并发的环境并且后续代码依赖于实际按日期排序的行，就会出现错误。

但是，如果我们运行与上面相同的指令序列，但将更新语句替换为以下内容:

update test set value = 'B', created_at = '2014-01-09 00:00:00' where id = 1;

...然后被阻止的查询返回正确的结果:

+----+-------+---------------------+
| id | value |     created_at      |
+----+-------+---------------------+
|  2 | A     | 2014-01-02 00:00:00 |
|  5 | A     | 2014-01-05 00:00:00 |
|  7 | A     | 2014-01-07 00:00:00 |
+----+-------+---------------------+

在这种情况下，被阻止的查询是否会执行两次，因为它的初始结果已失效？

我对 PostgreSQL 最感兴趣，但我也想知道其他支持行级锁定的 RDBMS 是否也是这种情况，例如 Oracle、SQL Server 和 MySQL。

Answer 1

这里发生了几件事。首先，这是记录在案的行为。其次，您看不到整个故事，因为您没有尝试更新 session “B”中的任何内容。

This seems like a violation of transaction isolation.

取决于您运行的隔离级别。 PostgreSQL's default transaction isolation level是 READ COMMITTED。

这是 documented behavior在 PostgreSQL 中。

It is possible for a SELECT command running at the READ COMMITTED transaction isolation level and using ORDER BY and a locking clause to return rows out of order. This is because ORDER BY is applied first. The command sorts the result, but might then block trying to obtain a lock on one or more of the rows. Once the SELECT unblocks, some of the ordering column values might have been modified, leading to those rows appearing to be out of order (though they are in order in terms of the original column values).

一个解决方法(也记录在案，相同的链接)是将 FOR UPDATE 移动到子查询中，但这需要表锁。

要了解 PostgreSQL 真正在这种情况下做了什么，请在 session “B”中运行更新。

create table test (
  id integer primary key,
  value char(1) not null,
  created_at timestamp not null
);
insert into test values
(1, 'A', '2014-01-01 00:00:00'),
(2, 'A', '2014-01-02 00:00:00'),
(3, 'B', '2014-01-03 00:00:00'),
(4, 'B', '2014-01-04 00:00:00'),
(5, 'A', '2014-01-05 00:00:00'),
(6, 'B', '2014-01-06 00:00:00'),
(7, 'A', '2014-01-07 00:00:00'),
(8, 'B', '2014-01-08 00:00:00');

A: begin;           /* Begin transaction A */B: begin;           /* Begin transaction B */A: select * from test where id = 1 for update; /* Lock one row */B: select * from test where value = 'B' order by created_at limit 3 for update; /* This query returns immediately since it does not need to return row with id=1 */B: select * from test where value = 'A' order by created_at limit 3 for update; /* This query blocks because row id=1 is locked by transaction A */A: update test set created_at = '2014-01-09 00:00:00' where id = 1; /* Modify the locked row */A: commit;B: update test set value = 'C' where id in (select id from test where value = 'A' order by created_at limit 3); /* Updates 3 rows */B: commit;

现在，看看表格。

scratch=# select * from test order by id; id | value |     created_at      ----+-------+---------------------  1 | A     | 2014-01-09 00:00:00  2 | C     | 2014-01-02 00:00:00  3 | B     | 2014-01-03 00:00:00  4 | B     | 2014-01-04 00:00:00  5 | C     | 2014-01-05 00:00:00  6 | B     | 2014-01-06 00:00:00  7 | C     | 2014-01-07 00:00:00  8 | B     | 2014-01-08 00:00:00

session “A”成功将 ID 为 1 的行更新为“2014-01-09”。 session “B”成功更新了值为“A”的剩余三行。 update语句获得了id号为2、5、7的锁；我们知道，因为那些是实际更新的行。较早的 select 语句锁定了不同的行——第 1、2 和 5 行。

如果您启动第三个​​ 终端 session ，您可以阻止 session B 的更新，并锁定第 7 行以进行更新。