On Mon, 2007-04-09 at 04:24, Alexey Nalbat wrote:
> Hello.
>
> I've encountered deadlock on postgresql 8.1. Here is the simple example.
>
> create table t1 (
> id1 integer primary key,
> val1 integer
> );
> create table t2 (
> id2 integer primary key,
> id1 integer references t1 on delete cascade,
> val1 integer,
> val2 integer,
> val3 integer
> );
> insert into t1 select
> generate_series(1,10) as id1;
> insert into t2 select
> generate_series(1,100) as id2,
> generate_series(1,10) as id1;
>
> Then three concurrent transaction start.
>
> /*1*/ begin;
> /*1*/ update t2 set val1=1 where id2=50;
> /*1*/ update t2 set val2=2 where id2=50;
> /*2*/ begin;
> /*2*/ update t2 set val1=1 where id2=40;
> /*2*/ update t2 set val2=2 where id2=40;
> /*2*/ commit;
> /*3*/ begin;
> /*3*/ update t1 set val1=1 where id1=10;
> /*1*/ update t2 set val3=3 where id2=50;
>
> Here we have deadlock for transactions 1 and 3.
That's not a deadlock, transaction 3 is simply waiting for transaction 1
to commit or rollback.
If you run a commit or rollback on transaction 1 then transaction 3 will
then be ready to commit or rollback as needed.