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)? – ypercubeᵀᴹ Nov 29 '16 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. – Craig Ringer Nov 29 '16 at 13:48
  • I got the same behaviour when adding the index. Postgresql version is 9.5.5 on ubuntu for the test. – Dennis Soegaard Dec 1 '16 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. – Dennis Soegaard Dec 1 '16 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.