Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why do you need to drop the index in order to copy the data? What exactly is that copy for? –  a_horse_with_no_name Dec 6 '11 at 16:14
    
It's a full copy but there may be many rows different so if we dont drop/recreate the process slows due to indexing. –  Cube Dec 7 '11 at 10:15
add comment

1 Answer

up vote 1 down vote accepted

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.

--Setup
CREATE TABLE ProcedureLock AS (SELECT logon_time FROM v$session WHERE rownum<=1);
UPDATE ProcedureLock SET Logon_Time = NULL;
COMMIT;

--Use
DECLARE
   vLogon_Time ProcedureLock.Logon_Time%Type;
   eInUse      Exception;

   PRAGMA EXCEPTION_INIT (eInUse, -54);
BEGIN
   Begin
       SELECT Logon_Time INTO vLogon_Time FROM ProcedureLock FOR UPDATE NOWAIT;
   Exception
      When eInUse Then
         DBMS_Output.Put_Line('The procedure is running.');
         Return;
   End;


   UPDATE ProcedureLock SET Logon_Time = 
    (SELECT min(logon_time) FROM gv$session WHERE SID = SYS_CONTEXT('USERENV','SID'))
   WHERE Logon_Time IS NULL;

   If (SQL%ROWCOUNT <> 1) Then
      DBMS_Output.Put_Line('The procedure is running or failed to complete.');
      Return;
   End If;

   COMMIT;

   DBMS_Output.Put_Line('The procedure is NOT running.');
   DBMS_Output.Put_Line('Run Procedure here.');

   UPDATE ProcedureLock SET Logon_Time = NULL;
   COMMIT;
END;
/

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.

share|improve this answer
    
Thanks for the answer. How do you ensure this lock table only ever has one row at a given time? Do I need a primary key too? You mention "update" but use insert, can you explain further? Thank you. –  Cube Dec 6 '11 at 11:17
    
Edited my answer. –  Leigh Riffel Dec 6 '11 at 16:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.