Обсуждение: basic stored proc/transaction question
My understanding is that a stored procedure does an implicit begin/commit when it executes. Maybe my brain isn't working so well this morning, because I can't figure out how I would do: begin; call stored proc; call another stored proc; commit; It seems that the transaction would be committed after the first call.
On 3/24/06, Ben <bench@silentmedia.com> wrote: > My understanding is that a stored procedure does an implicit begin/commit when > it executes. Maybe my brain isn't working so well this morning, because I can't > figure out how I would do: > > begin; > call stored proc; > call another stored proc; > commit; > > It seems that the transaction would be committed after the first call. > Nope. Unless you use the new SAVEPOINT stuff, the explicit transaction is the transaction. Any error in any function will rollback the whole thing. The commit happens at the explicit commit. Every SQL statement (such as calling a function) runs in an implicit transaction. Explicit transactions effectively "group" these implicit transactions such that any one failure causes them all to fail. - Ian
Well, that's awesome. Thanks! On Fri, 24 Mar 2006, Ian Harding wrote: > On 3/24/06, Ben <bench@silentmedia.com> wrote: >> My understanding is that a stored procedure does an implicit begin/commit when >> it executes. Maybe my brain isn't working so well this morning, because I can't >> figure out how I would do: >> >> begin; >> call stored proc; >> call another stored proc; >> commit; >> >> It seems that the transaction would be committed after the first call. >> > Nope. Unless you use the new SAVEPOINT stuff, the explicit > transaction is the transaction. Any error in any function will > rollback the whole thing. The commit happens at the explicit commit. > > Every SQL statement (such as calling a function) runs in an implicit > transaction. Explicit transactions effectively "group" these implicit > transactions such that any one failure causes them all to fail. > > - Ian >