Unexpected failure in transactions with isolation level serializable

Question

Unexpected failure in transactions with isolation level serializable

Hi,

I have a challenge with transactions in Postgresql when they have the isolation level SERIALIZABLE.

This is the transaction table and data:

CREATE TABLE "test_transaction"
(
  "id" bigserial PRIMARY KEY,
  "other_id" bigint NOT NULL,
  "created_time" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_time" timestamp with time zone NOT NULL DEFAULT now()
);

INSERT INTO "test_transaction"
    ("id", "other_id")
    VALUES
    (1, 1),
    (2, 2),
    (3, 1),
    (4, 2),
    (5, 1),
    (6, 2);

Start transaction A:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

Start transaction B:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;

Select the last transaction with "other_id"=1 in transaction A:

select 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time" 
from 
  "test_transaction" 
where 
  "test_transaction"."other_id" = 1 
order by 
  "test_transaction"."updated_time" desc limit 1;

Select the last transaction with "other_id"=2 in transaction B:

select 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time" 
from 
  "test_transaction" 
where 
  "test_transaction"."other_id" = 2 
order by 
  "test_transaction"."updated_time" desc limit 1;

Update "updated_time" where id = 1 in transaction A:

update 
  "test_transaction" 
set 
  "updated_time" = now()
where 
  "test_transaction"."id" = 5
returning 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time";

Update "update_time" where id = 2 in transaction B:

update 
  "test_transaction" 
set 
  "updated_time" = now()
where 
  "test_transaction"."id" = 6
returning 
  "test_transaction"."id", 
  "test_transaction"."other_id", 
  "test_transaction"."created_time", 
  "test_transaction"."updated_time";

Commit transaction A:

COMMIT;

Commit transaction B:

COMMIT;

Recieve an error in transaction B

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

Not sure what goes wrong in transaction B as I think that transaction A and B should work on each set and only lock sets where other_id equals 1 and 2 respectively. But it seems like the entire table is protected?

I have tested the same with "Repeatable Read" isolation level and which works perfectly. I can see that it should be "Phantom Read" that is allowed here.

Can someone shred some light on what goes on here as I expected it would work without any issues?

Answer 1

(sorry for the necro bumping)

I have a similar problem with PostgreSQL 12.8. From my understanding, it's because postgres would escalate the row lock to a page lock/table lock, based on the query plan.

https://www.postgresql.org/docs/current/transaction-iso.html

Predicate locks in PostgreSQL, like in most other database systems, are based on data actually accessed by a transaction. These will show up in the pg_locks system view with a mode of SIReadLock. The particular locks acquired during execution of a query will depend on the plan used by the query, and multiple finer-grained locks (e.g., tuple locks) may be combined into fewer coarser-grained locks (e.g., page locks) during the course of the transaction to prevent exhaustion of the memory used to track the locks.

I also tried to add an index, which didn't help. However, if I temporary use set enable_seqscan = off, to prevent the Seq scan, then I don't have the problem anymore (but I can't use that setting in production).

I know it's not an answer, but I hope it helps a little bit.