Skip to Main Content

SQL & PL/SQL

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!

Strange deadlock situation in multi-threaded application

Bjoern BergMar 19 2015 — edited Mar 19 2015

Hi all,

I have an application with five threads working on the same database instance. The application is responsible to import data from an external ressource to a table called temporary_archive.

After the data is validated and internally splitted and moved to other tables, the table temporary_archive should be cleaned up (this is done by a delete statement).

The delete-Statement is encapsulated in a function inside a package, because before the statement is executed, some checks are done. So every thread of the application can call this cleanup routine as needed. Here is a snippet to make more clear what is done:

begin

  if m_clean_all then

     delete from temporary_archive;

  else

     delete from temporary_archive

       where id = my_id and

             nvl(sequenz_nr, 0) = 0;

  end if;

exception when others then

  rollback;

end;

After a while of running, the application or better the database reports a deadlock situation while executing the statement

delete from temporary_archive

       where id = my_id and

             nvl(sequenz_nr, 0) = 0;

All five threads try to execute the delete-statement at the same time with different my_id.

I have tested this routine on two similar Oracle 11g database. On the one a long-time test with a big amount of data never leads to the deadlock, on the other database it fails after a few imports. Every Thread has its own Session.

My question is:

How could this lead to a deadlock if the my_id is different and different table rows should be accessed?

~ Björn

This post has been answered by Paul Horth on Mar 19 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2015
Added on Mar 19 2015
5 comments
483 views