Best practices: commit by database layer vs. commit by caller

[Matthew Houseman]

All,

What's the accepted best practice for persisting data? Commit by the
PL/SQL or commit by the caller (C#, Java, VB, etc.). I believe that
if the caller starts a transaction and calls PL/SQL that contains
commit/rollback that the XA Manager throws an exception. Since I
can't predict what the caller of my PL/SQL could do, I'm leaning
towards having the caller handle the transaction as a precondition of
calling into the database layer.

Comments are appreciated.

Matt Houseman

[Jim Kennedy]

"Matthew Houseman" <mh******@ix.netcom.com> wrote in message
news:73**************************@posting.google.c om...

All,

What's the accepted best practice for persisting data? Commit by the
PL/SQL or commit by the caller (C#, Java, VB, etc.). I believe that
if the caller starts a transaction and calls PL/SQL that contains
commit/rollback that the XA Manager throws an exception. Since I
can't predict what the caller of my PL/SQL could do, I'm leaning
towards having the caller handle the transaction as a precondition of
calling into the database layer.

Comments are appreciated.

Matt Houseman

Unless the entire pl/sql procedure is a transaction I would have the caller
set the transaction boundries. Then the expectation of what do do with
exception handling is clearer. Caller gets the exception and determines what
to do (rollback, retry, commit, ask the user something, log it etc.).
Jim