CODEWITHSUNDEEP
oracle-database
Ivan Zelenskyy
Ivan Zelenskyy

Reputation: 669

Transactions in Oracle

I have few stored procedures in Oracle database as third-party API, and I want to chain these api-calls in one transaction. Can I do it? As I understand from that answer I can use savepoint. But does it works if these API already have commit statements? Will Oracle roll back nested commits or not?

Also, how Oracle works with savepoint and concurrency? I.e. if we have diagram:

connection#1 (my api call)              connection#2 (3rd party api call)
savepoint sp1;
                                        savepoint sp2;
update t1 where id=1 set val=1;         update t1 where id=2 set val=2;
                                        commit; --done
call bad_stored_proc();
rollback to sp1;

What will happens here? Will rollback affect only row (id=1,val=1), or both rows?

Upvotes: 0

Views: 275

Answers (2)

James
James

Reputation: 3411

If the third party procedures call commit within the procedure, there is no way to run all of them as one transaction, without modifying the procedures themselves.

Oracle does not support nested commits or nested transactions. Commit means just that - you are committing to writing the changes to the database and losing the option to rollback the changes.

You can use a session parameter to check whether the third party procedures contain any commit statements.

alter session disable commit in procedure;

With this set, any procedures that attempt to commit will throw an error message.*

  • With thanks to Matthew McPeak for correcting my misconception of what this parameter does.

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17924

Oracle will not rollback nested commits. They're committed.

The best you can do (and it's not really that great) would be to wrap the 3rd party API in a procedure and mark that procedure with the PRAGMA AUTONOMOUS_TRANSACTION. That would limit the 3rd party API to committing only its own work. That means, if you rolled back, the 3rd party transaction work would be committed but any other work you did wouldn't be.

That is generally a really bad thing, because it will leave your data logically corrupted in the vast majority of design situations. I mention it in the unlikely chance that are facing a design situation where you can get away with it.

Upvotes: 1

Related Questions