We have a PL/SQL stored procedure that is called on user demand which takes a copy of another table (ie. drop index > delete (not truncate) > insert > create index) and we've noticed the odd race condition where an index will not always get recreated.
This leaves us a table with no indexes, so future calls to the procedure fail (yes we could simply ignore the failed drop index). However, I'd like to get to the bottom of why the table is losing its indexes.
I don't think LOCK TABLE
is the answer because after every DDL statement (eg. drop index) I'd keep losing the lock due to the implicit commit.
Best Answer
If you keep a separate table to maintain whether your procedure is running or not, then you only need to keep a lock as long as it takes you to update the table to ensure that another session is not doing the same. Here is some code to setup the table and then use it to run your procedure.
First we lock the new ProcedureLock table and then update it. If either of these fail we know the procedure is running and therefore we should not run it again.