I'm looking for a way to find out if there are uncommited INSERT, UPDATE or DELETE statements in the current session. One way would be to check v$lock with the current sid, but that requires read access to v$lock, which is a problem if the DBA doesn't want to grant it. Any other ways (other than keeping track of all database commands issued by the application)?
7 Answers
The easiest and most reliable solution is to try and start a transaction and see it if succeeds. If some code already started a transaction but has not yet issued any DML, then the V$TRANSACTION view won't show anything.
In this example below, I handle the exception to raise a user-defined application error. To defer to an existing exception handler, just do a SET TRANSACTION and then immediately COMMIT to undo it.
DECLARE
transaction_in_progress EXCEPTION;
PRAGMA EXCEPTION_INIT(transaction_in_progress, -1453);
BEGIN
SET TRANSACTION NAME 'CHECK_FOR_TRANSACTION_ALREADY_SET';
COMMIT; -- end transaction
EXCEPTION
WHEN transaction_in_progress THEN
RAISE_APPLICATION_ERROR(-20000,'Transaction is already in progress');
END;
/
Related