Re: deadlock detected during insert

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.