Isolation Level and Explicit Locking: Unexpected Serialization Error

Question

I am writing a web application, and I've been experimenting with wrapping SQL statements from every web request with a transaction with ISOLATION LEVEL REPEATABLE READ, to find where my web application might be doing non-repeatable reads. My plan was not to retry in case of a non-repeatable read, and just report a server-side error (500) to the user and log the information (since I expect this to be very rare).

At the same time, there are places in my code where I use explicit locking (SELECT ... FOR UPDATE) to make sure I serialize access correctly and don't cause unrepeatable reads.

Combining the two ideas together, however, is giving me unexpected results.

Below is a minimal example:


+--------------------------------------------------+--------------------------------------------------+
| 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                             |
+--------------------------------------------------+--------------------------------------------------+

My expectation is that since session 2 didn't do any reads before that SELECT statement, and since that statement only returns after session 1 has done its update, then session 2 should see the updated version of the table, and that would make it a repeatable read.

I take it that, most likely, Postgres is taking a version when BEGIN is ran, not when it acquires the lock for the first SELECT.

My questions:

Is my understanding correct?
Is there a way to make Postgres behave the way I expect?
Would this be considered a bug, or is this working as intended?

user_9661424 · Answer

From "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.

So yes your understanding seems correct if by BEGIN you mean the start of the transaction. And no this is not a bug but working as intended and documented.

For what I understand, READ COMMITTED, the default, should do what you want. Note that after committing the first transaction in client 1, a SELECT FOR UPDATE is blocked until client 2 commits or rollbacks as it's SELECT FOR UPDATE now succeeded. So the first transaction in client 2 will read the same values (unless itself changes them) until the end of the transaction.

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                   |

Isolation Level and Explicit Locking: Unexpected Serialization Error

1 Answers