Thread: deadlock detected during insert

deadlock detected during insert

From
"A.Bhuvaneswaran"
Date:
Hi,

I am using pgsql 7.2.3 on redhat linux 7.3.

I am facing the deadlock detected error in transaction during insert
command. How can i fix this?

After googling, i found that when two simultaneous transactions try to
wait for completion of one another(update commands), the deadlock would be
detected and one transaction would be rolled back. Locking the table in
the same order must help.

But in my case it is insert command? I googled, but didnot get any related
links. Do i have to lock the table? Which lock mode should i use then?

TIA.

regards,
bhuvaneswaran



Re: deadlock detected during insert

From
Stephan Szabo
Date:
On Thu, 6 Mar 2003, A.Bhuvaneswaran wrote:

> Hi,
>
> I am using pgsql 7.2.3 on redhat linux 7.3.
>
> I am facing the deadlock detected error in transaction during insert
> command. How can i fix this?

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.


Re: deadlock detected during insert

From
"A.Bhuvaneswaran"
Date:
> 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.

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

regards,
bhuvaneswaran


Re: deadlock detected during insert

From
Stephan Szabo
Date:
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.


Re: deadlock detected during insert

From
"A.Bhuvaneswaran"
Date:
> > 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?

regards,
bhuvaneswaran

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


Re: deadlock detected during insert

From
Stephan Szabo
Date:
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.