Locking past the commit point?

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

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!

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;