Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Deadlock issue in the DB because of concurrent data deletion

988847Feb 4 2013 — edited Feb 4 2013
I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.

The example and the table structure is given below.

Col1 of tab1 is foreign key to col1 of tab2.
Commit will not be done until all the below dml scripts are executed in both environment.

1st session:

Delete from tab1 where col1=1001;
Delete from tab2 where col1=2001;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1002;
Delete from tab2 where col1=2002;

Result: Deletion successful

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1004;
Delete from tab2 where col1=2004;

Result: Query is executing for a longer time.

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Query is not executed and throws Deadlock in the back end

Can someone provide me a suggestion/Solution what will be the issue in this execution and steps to overcome it
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 4 2013
Added on Feb 4 2013
3 comments
708 views