Postgresql – Isolation level serializable not working as expected

isolation-levelpostgresqltransaction

I have this table 

create table testing(
c1 text,
c2 text);

I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order
Transaction 1

BEGIN; -- t0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t2
SELECT * FROM testing where c2 = 'rand'; -- t4
INSERT INTO testing VALUES ('rand', 'xyz'); -- t6
COMMIT; -- t8

Transaction 2

BEGIN; -- t1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t3
SELECT * FROM testing where c1 = 'rand1'; -- t5
INSERT INTO testing VALUES ('rand1', 'abc'); -- t7
COMMIT; -- t9

After t9 I get this error

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

But if you see the statements, they are operating on difference values rand and rand1. It doesn't make sense for this error to be thrown as they can never end up in a bad state.

Is my understanding correct?

Best Answer

Your understanding is fundamentally correct. What you didn't realize is that there can be false positive results when with the SERIALIZABLE isolation level.

The documentation says:

While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution.

In this particular case, I would assume that the problem is that you don't have an index on c1 and c2 or PostgreSQL chooses to do a sequential scan anyway. As you can read further down on the same page:

  • A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.
Related Question