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