Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction isolation and table contraints

Transaction isolation and table contraints

From
Konstantin Knizhnik
Date:
Hi hackers,

I wonder if it is considered as correct behavior that transaction 
conflict detection depends on presence of primary key:

create table t(pk integer, val integer);
insert into t values (1,0),(2,0);

Session1: begin isolation level serializable;
Session2: begin isolation level serializable;
Session1: update t set val=val+1  where pk=1;
Session2: update t set val=val+1  where pk=2;
Session1: commit;
Session2: commit;
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.


Now let's repeat the same scenario but with "pk" declared as primary key:

create table t(pk integer primary key, val integer);
insert into t values (1,0),(2,0);

Session1: begin isolation level serializable;
Session2: begin isolation level serializable;
Session1: update t set val=val+1  where pk=1;
Session2: update t set val=val+1  where pk=2;
Session1: commit;
Session2: commit;

Now both transactions are succeeded.
Please notice, that even if it is expected behavior, hint in error 
message is not correct, because transaction is actually aborted and 
there is no chance to retry it.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: Transaction isolation and table contraints

From
"David G. Johnston"
Date:
On Wed, Nov 25, 2020 at 8:14 AM Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:
Hi hackers,

I wonder if it is considered as correct behavior that transaction
conflict detection depends on presence of primary key:

create table t(pk integer, val integer);
insert into t values (1,0),(2,0);

ERROR:  could not serialize access due to read/write dependencies among
transactions
[...]
Now let's repeat the same scenario but with "pk" declared as primary key:

create table t(pk integer primary key, val integer);
insert into t values (1,0),(2,0);

Now both transactions are succeeded.

From the docs:

"A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures."

The two seem possibly related (I'm not experienced with using serializable)

 
Please notice, that even if it is expected behavior, hint in error
message is not correct, because transaction is actually aborted and
there is no chance to retry it.


It is technically correct, it just doesn't describe precisely how to perform said retry, leaving that up to the reader to glean from the documentation.

The hint assumes users of serializable isolation mode are familiar with transaction mechanics.  In particular, the application needs to be prepared to retry failed transactions, and part of that is knowing that PostgreSQL will not automatically rollback a failed transaction for you, it is something that must be done as part of the error detection and recovery infrastructure that is needed when writing applications that utilize serializable.

David J.