Обсуждение: deadlock detected during insert
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
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.
> 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
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.
> > 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.
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.