Where should I commit a transaction -- in the Stored Procedure or in the calling application code?

Question

I'm using PHP + Oracle and was wondering if there are any recommendations on where to commit my transactions. I call stored procedures to do all my inserts/updates/deletes, and currently am committing at the end of my stored procedures.

I was wondering:

1. Is there any difference between calling commit/rollback in my stored procedure vs calling oci_commit / oci_rollback in my PHP code based on the success of the stored procedure call.

2. Which is preferable? Originally I was thinking in the stored procedures themselves, but now I'm wondering, if there's no difference, perhaps it would give me more flexibility to commit in the calling application code since I could call several stored procedures in a single transaction rather than having to write new stored procedures every time I want to mix/match a variety of SQL statements in a single transaction.

Thoughts?

Answer 1

Not sure about PHP/Oracle but our SQL procs check to see if they're already in a transaction. If they are they just do what they do. If not they create a transaction and commit/rollback themselves. This gives us flexibility, especially for procs that are called from multiple different processes.

Answer 2

I definitely agree with option 2, for the reasons you give. Having each stored procedure act as a separate transaction can be too limiting sometimes. Tom Kyte would back option 2 as well: see this AskTom thread for example.

Answer 3

Option 2 is the way to go as a general rule. If you have your commits inside your stored procedures, you are making a decision that no matter who calls me, I know better and should commit any open transactions when I finish my work.

Say you have some process that has multiple steps and needs to either commit them all or roll them all back. If that process happens to call one of your stored procedures that has a commit in it as part of doing its work, then everything up to that point could be committed by that stored procedure.

It is almost always preferable to let the caller decide about what should happen with the transaction. One normal exception is an autonomous transaction inside a procedure for cases that need it specifically.