postgresql - 隔离级别和显式锁定 : Unexpected Serialization Error

Question

我正在编写一个 Web 应用程序，并且我一直在尝试将来自每个 Web 请求的 SQL 语句与带有 ISOLATION LEVEL REPEATABLE READ 的事务进行包装，以找出我的 Web 应用程序可能不执行的操作-可重复读取。我的计划是在不可重复读取的情况下不重试，而只是向用户报告服务器端错误 (500) 并记录信息(因为我预计这种情况非常罕见)。

与此同时，我在代码中的某些地方使用了显式锁定 (SELECT ... FOR UPDATE) 以确保我正确地序列化访问并且不会导致不可重复的读取。

然而，将这两个想法结合在一起，给了我意想不到的结果。

下面是一个最小的例子:

+--------------------------------------------------+--------------------------------------------------+
| Session 1                                        | Session 2                                        |
+--------------------------------------------------+--------------------------------------------------+
| BEGIN;                                           |                                                  |
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |                                                  |
| SELECT * FROM users WHERE id = 1 FOR UPDATE;     |                                                  |
| (returns as expected)                            |                                                  |
+--------------------------------------------------+--------------------------------------------------+
|                                                  | BEGIN;                                           |
|                                                  | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
|                                                  | SELECT * FROM users WHERE id = 1 FOR UPDATE;     |
|                                                  | (blocks as expected)                             |
+--------------------------------------------------+--------------------------------------------------+
| UPDATE users SET name = 'foobar' WHERE id = 1;   |                                                  |
| COMMIT;                                          |                                                  |
| (works as expected)                              |                                                  |
+--------------------------------------------------+--------------------------------------------------+
|                                                  | ERROR:  could not serialize access due           |
|                                                  | to concurrent update                             |
+--------------------------------------------------+--------------------------------------------------+

我的期望是，由于 session 2 在该 SELECT 语句之前没有进行任何读取，并且由于该语句仅在 session 1 完成更新后返回，因此 session 2 应该会看到表的更新版本，这将使它成为可重复读取的。

我认为，Postgres 很可能在运行 BEGIN 时采用一个版本，而不是在它为第一个 SELECT 获取锁时采用一个版本。

我的问题:

• 我的理解正确吗？
• 有没有办法让 Postgres 的行为符合我的预期？
• 这会被视为错误，还是按预期工作？

Answer 1

来自 "13.2.2. Repeatable Read Isolation Level" :

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

    ERROR:  could not serialize access due to concurrent update

because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.

是的，如果 BEGIN 是指交易的开始，那么您的理解似乎是正确的。不，这不是错误，而是按预期和记录工作。

据我所知，默认情况下，READ COMMITTED 应该可以执行您想要的操作。请注意，在客户端 1 中提交第一个事务后，SELECT FOR UPDATE 将被阻止，直到客户端 2 提交或回滚，因为它的 SELECT FOR UPDATE 现在已成功。因此客户端 2 中的第一个事务将读取相同的值(除非它自己更改它们)直到事务结束。

Client 1                                        | Client 2
------------------------------------------------+------------------------------------------------
BEGIN TRANSACTION;                              |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
SELECT * FROM users WHERE id = 1 FOR UPDATE;    |
                                                | BEGIN TRANSACTION;
                                                | SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
                                                | SELECT * FROM users WHERE id = 1 FOR UPDATE;
                                                | -- blocks
UPDATE users SET name = 'foobar' WHERE id = 1;  |
COMMIT;                                         |
                                                | -- name = 'foobar' is read
BEGIN TRANSACTION;                              |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
SELECT * FROM users WHERE id = 1 FOR UPDATE;    |
-- blocks                                       |
                                                | SELECT * FROM users WHERE id = 1 FOR UPDATE;
                                                | -- name = 'foobar' is read
                                                | COMMIT;
UPDATE users SET name = 'foobaz' WHERE id = 1;  |
-- name = 'foobaz' is written                   |