Locking past the commit point?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Locking past the commit point?

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    Hi:

    Is there any way to lock a table past the commit point?. I want to lock a table run some DMLs (say delete) and release the table, when done. I do not want to release the lock when issuing a commit, because after commiting and bofore re-acquiring the lock, somebody else could lock/use the table.

    I want the table to be locked for the entire duration of the application.

    I am running Ora 8.1.6

    Thanks

  2. #2
    Join Date
    Nov 2000
    Posts
    212
    you can try to synchronize your concurent processes with SYS.DBMS_LOCK package: it provides locks across commits.

    But remember, then YOU ARE RESPONSIBLE FOR CODING APPLICATION SUCH THAT IT RELEASES LOCKS!


  3. #3
    Join Date
    Nov 2000
    Posts
    212

    app. transparent solution #1

    here is solution how to make use of DBMS_LOCK such that it is transparent to application:

    create a trigger on the table under consideration BEFORE upd, ins,del and request shared lock.

    In your code request Exclusive mode for the same lock. Then no other session can DML your table.

    Source eg.:

    Trigger:
    CREATE OR REPLACE TRIGGER grd_diary_113_old
    BEFORE INSERT OR DELETE OR UPDATE
    ON diary_113_old
    REFERENCING NEW AS NEW OLD AS OLD
    declare
    rc INTEGER;
    Begin
    rc:=DBMS_LOCK.REQUEST(2000001111, DBMS_LOCK.SS_MODE, DBMS_LOCK.MAXWAIT, TRUE);
    if rc in (0, 4) then null;
    else
    Raise_Application_Error(-20000, 'TRG.: GRD_DIARY_113_OLD DBMS_LOCK.REQUEST RC:'||RC);
    end if;

    End;
    /

    App code to get exclusive lock:
    rc:=DBMS_LOCK.REQUEST(2000001111, DBMS_LOCK.X_MODE, 10/*DBMS_LOCK.MAXWAIT*/, FALSE);
    if rc in (0, 4) then null; else Raise_Application_Error(-20000, 'REORG_TAB LOCK DBMS_LOCK.REQUEST RC:'||RC); end if;

    To release lock:
    rc:=DBMS_LOCK.RELEASE(2000001111); if rc in (0, 4) then null; else
    Raise_Application_Error(-20000, 'REORG_TAB LOCK DBMS_LOCK.RELEASE RC:'||RC);
    end if;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width