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
BEGIN; -- t0 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t2 SELECT * FROM testing where c2 = 'rand'; -- t4 INSERT INTO testing VALUES ('rand', 'xyz'); -- t6 COMMIT; -- t8
BEGIN; -- t1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t3 SELECT * FROM testing where c1 = 'rand1'; -- t5 INSERT INTO testing VALUES ('rand1', 'abc'); -- t7 COMMIT; -- t9
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
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?