1

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?

  • Does this change if you make c1 the primary key? – a_horse_with_no_name Jul 4 '19 at 5:37
  • @a_horse_with_no_name : Will give it a try – Ashwin Jul 6 '19 at 5:06
3

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.

Your Answer

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

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