3

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?

4
  • This is interesting. Does it also happen if you add an index on (other_id, updated_time)? Commented Nov 29, 2016 at 11:49
  • 3
    Side-note: SERIALIZABLE doesn't promise never to cancel a transaction that is safe. Applications should always be ready to retry any transaction when using SERIALIZABLE isolation. Then again, well written apps should be ready to retry any xact in READ COMMITTED too, to cope with server restarts, deadlock aborts, etc. Keep all the data until you get commit confirmation. Be ready to retry. Commented Nov 29, 2016 at 13:48
  • I got the same behaviour when adding the index. Postgresql version is 9.5.5 on ubuntu for the test. Commented Dec 1, 2016 at 13:41
  • Thanks Craig, that did clarify things a bit. Seems like Postgresql does not apply any "smart" logic in this scenario. Currently using a retry strategy but unfortunate there are quite a lot of activity and hence a lot of collisions. Commented Dec 1, 2016 at 13:49

1 Answer 1

0

(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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.