CODEWITHSUNDEEP
oracle-databasestored-proceduresconcurrency
deejay
deejay

Reputation: 575

how to unlock locked tables explicitly in oracle

I have a master stored procedure which calls multiple stored procedures to write data on multiple tables, around 9-10 in one go. And once all the inserts are done, there is one commit for all of them.
I want to use data concurrency and lock tables in individual sub procedures, which does not have any commit, so

LOCK TABLE table_name IN lock_mode

will work, but will hold the table until rest of the data is been inserted in the respective tables called after this and the final commit or rollback is called, which is not a good idea. Also I don't have dbms_lock opened.

Will locking all the tables in my master stored procedures, or locking the tables in the respective sub-stored procedures is the only option??

My master stored procedure looks like this 

PROCEDURE POPULATE_ALL(P_ASOFDATE DATE, P_ENTITY VARCHAR2) IS
    BEGIN
      POPULATE_ABC_BOOK(P_ASOFDATE);
      POPULATE_XYZ(P_ASOFDATE, P_ENTITY);
      POPULATE_DEF(P_ASOFDATE, P_ENTITY);
      POPULATE_AAA(P_ASOFDATE, P_ENTITY);
    commit;
    EXCEPTION
     WHEN OTHERS THEN
       rollback;
       P_ERROR := SQLERRM;
       RAISE_APPLICATION_ERROR(-20001,
                              '*** Unexpected Error in POPULATE_ALL -->' ||
                              P_ERROR);
   END POPULATE_ALL;

where POPULATE_XYZ is populating XYZ table.

Upvotes: 1

Views: 6390

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

It looks like you don't need locking at all. Just do your inserts without any explicit locking.

There is no way to unlock table in the middle of transaction. This operation has no ANY sense in Oracle; it can be useful only if database supports dirty reads. Table lock finishes only if

  • commit,
  • rollback or
  • rollback to savepoint before lock established

executed.

Upvotes: 1

Related Questions