Re: deadlock detected during insert - Mailing list pgsql-admin

From Stephan Szabo
Subject Re: deadlock detected during insert
Date
Msg-id 20030310082152.F28512-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: deadlock detected during insert  ("A.Bhuvaneswaran" <bhuvansql@myrealbox.com>)
List pgsql-admin
On Sat, 8 Mar 2003, A.Bhuvaneswaran wrote:

>
> > > Can you able to give some test cases? Here i have tried, but unable to
> > > detect deadlock.
> >
> > Not a complete or tested example, but:
> > create table a (a int primary key);
> > create table b ( b int references a);
> > insert into a values (1);
> > insert into a values (2);
> > Transaction 1: begin;
> > Transaction 2: begin;
> > Transaction 1: insert into b values (1);
> > Transaction 2: insert into b values  (2);
> > Transaction 1: insert into b values (2);
> > Transaction 2: insert into b values (1);
> >
> > - This really shouldn't be a deadlock, but given the locking level the
> > foreign keys currently use I'm pretty sure it'd cause one.
>
> It shouldn't be, but it is detected. But i am able to eliminate it using
> the lock in share row exclusive mode. Am i right using this lock?

Do you mean you're grabbing a lock with lock table (in a consistent
order)? That'll prevent deadlocks if done consistently although it gives
concurrency problems (serialization of the transactions) which may or may
not be fine for your application.


pgsql-admin by date:

Previous
From:
Date:
Subject: backing up and restoring
Next
From: Tom Tom
Date:
Subject: multiple $PGDATA dir's for one! postmaster?