Why two transactions with isolation serializable are blocked when writing to different lines

Question

I have problem with transactions in my app.

My first transaction:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM public.owner WHERE id = 15;
UPDATE public.owner SET current_cat = 2 WHERE id = 15;
COMMIT;

My second transaction:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM public.owner WHERE id = 16;
UPDATE public.owner SET current_cat = 4 WHERE id = 16;
COMMIT;

Rough example of my tables:

CREATE TABLE assortment.cat (
    id int not null,
    name varchar not null
);

CREATE TABLE assortment.owner (
    id int not null,
    fio varchar not null,
    current_cat int not null
);

Owners can swap cats :) So, if begin two transactions step by step cancurrently (begin of first transaction -> begin of second transaction -> select of first transaction -> select of second transaction etc.) then first transaction ending is successful but second transaction will fail:

could not serialize access due to read/write dependencies among transactions. Reason code: Canceled on identification as a pivot, during commit attempt

Two transactions have changed different lines, why does blocking hapend? I expect both transactions to complete successfully.

I will be glag of any help! :)

P.S. I'm using PostgreSQL

I think your [question is answered here](https://stackoverflow.com/a/27349418/11902742). — MatKuhr, Mar 23 '20 at 16:30

score 0 · Answer 1 · answered Mar 23 '20 at 17:24

Serializable isolation level guarantees against false negatives. It does not guarantee against false positives. If it had to do the latter, time and/or memory usage could explode. Code correctly written to use serializable isolation must always be prepared to retry transactions. So occasionally retrying due to a false positive is not a big deal, unless it happens so often as to be a performance problem.

This type of false positive is far more likely in microscopic toy examples than in real-world cases.

score 0 · Accepted Answer · answered Mar 23 '20 at 17:29

Do you have an index on owner(id) ?

If I create the table exactly as above given I can reproduce the issue:

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 I create it this way:

create table owner
(
id int not null primary key,
fio varchar not null,
current_cat int not null
);

I cannot reproduce the issue.

See first answer in Why does PostgreSQL serializable transaction think this as conflict?

Thanks! Your link helped me :) – kolliath Apr 26 '20 at 21:51 — kolliath, Apr 26 '20 at 21:51

Why two transactions with isolation serializable are blocked when writing to different lines

2 Answers2