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

This seems like a violation of transaction isolation.

取决于您正在运行的隔离级别. PostgreSQL’s default transaction isolation level是READ COMMITTED.

这是PostgreSQL中的documented behavior.

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在这种情况下的确实做了什么,请在会话“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,'2014-01-02 00:00:00'),(3,'B','2014-01-03 00:00:00'),(4,'2014-01-04 00:00:00'),(5,'2014-01-05 00:00:00'),(6,'2014-01-06 00:00:00'),(7,'2014-01-07 00:00:00'),(8,'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

会话“A”成功将id为1的行更新为“2014-01-09”.会话“B”成功更新了值为“A”的其余三个行.获取的更新语句锁定了id号2,5和7;我们知道因为那些是实际更新的行.较早的select语句锁定了不同的行 – 行1,2和5.

如果启动第三个终端会话,则可以阻止会话B的更新,并锁定第7行以进行更新.