CODEWITHSUNDEEP
postgresql
Ivan Alikin
Ivan Alikin

Reputation: 69

No serialize access error is raised in PostgreSQL while it should be

I have a concern about SERIALIZABLE isolation level behavior in a situation similar to one described below.

The table:

CREATE TABLE concurrency_test (
    id serial PRIMARY KEY,
    sum INT NOT NULL
);

Queries:

+------+-------------------------------------------------+-------------------------------------------------+
| Step |                  Connection #1                  |                  Connection #2                  |
+------+-------------------------------------------------+-------------------------------------------------+
|    1 |                                                 | START TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
|    2 |                                                 | INSERT INTO concurrency_test (sum) VALUES(400); |
|    3 | START TRANSACTION ISOLATION LEVEL SERIALIZABLE; |                                                 |
|    4 | SELECT SUM("sum") FROM concurrency_test;        |                                                 |
|    5 |                                                 | COMMIT;                                         |
|    6 | INSERT INTO concurrency_test (sum) VALUES(300); |                                                 |
|    7 | COMMIT;                                         |                                                 |
+------+-------------------------------------------------+-------------------------------------------------+

At Step 4, the SELECT query cannot see the new row inserted by another transaction because it hasn't been committed yet. So the following INSERT query at Step 6 is relying on stale data.

I expected that the SERIALIZABLE isolation level would resolve this and wouldn't allow me to successfully commit the transaction at Step 7 and return 40001 error... But to my surprise, it did not raise any error.

Why did it happen and how can I achieve the behavior that I need?

Upvotes: 2

Views: 280

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248175

The SELECT in step 4 does not rely on stale data, because the modification has not been committed yet. Until a transaction commits, the database acts as if it nothing has happened yet.

This is guaranteed by the READ COMMITTED isolation level and all higher ones. (PostgreSQL does not allow dirty reads, so you will get this behavior even if you request the READ UNCOMMITTED isolation level.)

Moreover, SERIALIZABLE means that the result of all transactions is guaranteed to be the same as that of some serialized execution of the transactions. And that is clearly the case here: If we serialize by executing the transaction on connection #1 before the one on connection #2, we get the same result.

It should never be necessary, but if you want to reduce concurrency by forcing a serialized execution, add the following statement immediately after each START TRANSACTION:

LOCK TABLE concurrency_test IN ACCESS EXCLUSIVE MODE;

Upvotes: 1

Related Questions