Re: deadlock detected during insert

On Fri, 7 Mar 2003, A.Bhuvaneswaran wrote:

>
> > If you're using foreign keys on the table that you're inserting into it's
> > possible that it's waiting  on another transaction doing an insert to the
> > same key or on a change to the referenced table.
>
> 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.

> BTW, do we have any link about deadlock detection during insert command as
> we have for update command?

Not sure really.