Skip to Main Content
  • Questions
  • How to find out, what stored procedure does a commit

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Tymur.

Asked: April 26, 2017 - 6:35 am UTC

Last updated: May 03, 2017 - 1:05 am UTC

Version: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

This may sound silly, but I'm unable to figure out what damn stored procedure (or something else?) performs a commit after I manually execute another stored procedure.

There is a complicated business logic implemented using stored procedures and, unfortunately, triggers; and the code is partially a decade old.

There is a problem I'm trying to debug, so I execute a stored procedure (which amends multiple tables), look at the results, and execute a `rollback` -- but the changes are somehow already committed. Is there a way to easily find out, what did this commit (besides from obvious going through all the code)?

and Connor said...

Throw a trace on the program. In the trace file you would see lines such as:

XCTEND rlbk=0, rd_only=0, tim=1265746141389


which means a transaction ended, and rlbk=0 means it was a commit.

If then tracking back through trace file is not sufficient to locate the procedure call, then if the commit is preceded by a (say) sql statement, you can use that SQL_ID to probe V$SQL which will contain the PROGRAM_ID, PROGRAM_LINE# columns which will point to the PLSQL program unit.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternative

Gary, April 28, 2017 - 4:27 am UTC

Another option is use a deferred constraint to force the COMMIT to fail.


create or replace procedure test_dc_one is
begin
  commit;
end;
/
create table defcon_one (id number(1));

alter table defcon_one add constraint defcon_one_ck check (id != 1) initially deferred;

insert into defcon_one(id) values (1);

exec test_dc_one


When the procedure tries to do a commit, it will need to check the deferred constraint. Because it is violated, the commit fails and raises an error.

This won't work if the procedure does a rollback of your pending insert. If the procedure handles the error silently, that's a separate problem :)
Connor McDonald
April 28, 2017 - 10:45 pm UTC

nice touch

many commits in AUTONOMOUS_TRANSACTION

Tymur Gubayev, April 28, 2017 - 6:57 am UTC

I was able to generate the tracefile, but there are lots of lines like following.

XCTEND rlbk=0, rd_only=0, tim=14473904665992


This is due to extensive logging done with
pragma AUTONOMOUS_TRANSACTION;
and also dbms_lock.

I tried using
TKPROF: Release 12.1.0.1.0 - Development on Fr Apr 28 08:35:09 2017
to get the trace in more readable form, but it just crashes in oraclient12.dll 12.1.0.1
timestamp 51cd5302 with exception code c0000005, offset 00000000004681ba.
Connor McDonald
April 28, 2017 - 10:49 pm UTC

Well, definitely tkprof shouldn't crash :-) The trace files are upward compatible, so you should be able to run tkprof in an 12.1.0.2 home or similar.


(should be a comment in a thread)

Tymur Gubayev, May 02, 2017 - 8:08 am UTC

(I'm sorry for a new "Review" -- I can't find a way to comment on a "Followup").

"Well, definitely tkprof shouldn't crash :-) The trace files are upward compatible, so you should be able to run tkprof in an 12.1.0.2 home or similar."

I've tried tkprof.exe from DB Versions 11.2, 12.1 and 12.2. The trace file is from 12.1. Every version of tkprof just crashed.

Either way, I'll use the "deferred constraint" suggested by Gary.

And, in case anyone is interested, the `commit` causing troubles was inside DBMS_LOCK.ALLOCATE_UNIQUE.
Connor McDonald
May 03, 2017 - 1:05 am UTC

Thanks for getting back to us on that.

"And, in case anyone is interested, the `commit` causing troubles was inside DBMS_LOCK.ALLOCATE_UNIQUE"

Well that *is* documented :-)

https://docs.oracle.com/database/121/ARPLS/d_lock.htm#ARPLS66771


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library