Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 19, 2016 - 8:40 am UTC

Last updated: September 19, 2016 - 4:02 pm UTC

Version: 10

Viewed 1000+ times

You Asked

hi ,
below insert query is causing deadlock..
insert into T_POLICY_GEN_CUST (UPDATE_TIME, INSURED_FLAG, PAYER_FLAG, INSERT_TIME, POLICY_ID, PARTY_ID) values (:1, :2, :3, :4, :5, :6)

can you please give me some detailed information about why this INSERT is causing deadlock..
AND Any suggestions to avoid this..
primary key(POLICY_ID, PARTY_ID)
Foreign key(POLICY_ID)

and Connor said...

The most common cause for deadlocks is often a foreign key related issue (in particular a missing index).

Some good info here

https://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

or perhaps a Bitmap index in place

Rajeshwaran, Jeyabal, September 20, 2016 - 1:39 pm UTC

demo@ORA11G> create table t (x int, y int);

Table created.

demo@ORA11G> create bitmap index t_idx on t(x);

Index created.

demo@ORA11G> insert into t(x,y) values(1,1);

1 row created.

demo@ORA11G> declare
  2     pragma autonomous_transaction;
  3  begin
  4     insert into t(x,y) values(1,55);
  5     rollback;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4


demo@ORA11G>