Re: Changing the transaction isolation level within the stored

On Thu, 26 Jan 2006, Markus Schaber wrote:


> AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
> the only way to introduce deadlocks is to issue LOCK commands to take
> locks manually. And for this rare case, PostgreSQL contains a deadlock
> detection routine that will abort one of the insulting transactions, and
> the others can proceed.

That's not true.  See all the complaints about pre-8.1 foreign keys and
the row locks taken out by FOR UPDATE as an example.

A simpler example than the one given before (with potential timing) is:
create table t1 (a int);
create table t2 (a int);
insert into t1 values(1);
insert into t2 values(1);

T1: begin;
T2: begin;
T1: update t1 set a=3;
T2: update t2 set a=3;
T1: update t2 set a=2;
T2: update t1 set a=2;