Re: basic stored proc/transaction question

[Ben <bench(at)silentmedia(dot)com>]

Well, that's awesome. Thanks!

On Fri, 24 Mar 2006, Ian Harding wrote:

> On 3/24/06, Ben <bench(at)silentmedia(dot)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