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

oracle, php, stored-procedures, transactions

Solution

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.

Problem

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: 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. 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?

Original source