Roelof Osinga wrote: > "Anhaus, Thomas" wrote: > > > > ... > > >> Note : The <commit statement> and <rollback statement> > are NOT permitted > > in > > >> a > > >> stored procedure. > > > ... > > IMO every stored procedure therefore should open a > subtransaction which is > > ended successfully only if no unexpected error occurs. > > Otherwise the subtransaction should be rolled back : > > > > CREATE DBPROC EXAMPLE AS ... > > SUBTRANS BEGIN; > > ... > > IF no_error > > THEN > > SUBTRANS END > > ELSE > > SUBTRANS ROLLBACK; > > I could not agree more, but I must confess I find myself > bedazzled :-). > For me a subtransaction is a nested transaction, which is a > transaction > like any other albeit smaller <g>. > > Yet the (way) above can only make sense if subtransactions are not > transactions as such for then there could be no rollback in a stored > proc. But then if they are not transactions how, if at all, can > they provide for atomicity? Yet if they do that implies that rollback > is allowed in a stored proc. Which begs the question that since not > all transactions seem to've been created equal, what about atomicity? > Are some atoms more, ehm, "atomier" than others? Protons, muons,...? > > I must be missing something here and though I think I've got an > inkling what, namely a semantic interpretation, ... ? > > I have the feeling someone went overboard in trying to prevent > confusement over scope. But that's just me ;).
Ok, at first, please see http://www.sapdb.org/htmhelp/e0/ed9036dfe4b903e10000009b38f889/frameset.htm for a description of transactions and subtransactions. You open a transaction always implicitly - if you start your session - if you have committed/rollbacked the transaction before connect to session insert into t1 subtrans begin insert into t2 values (2) update t3 set col = 2 subtrans rollback insert into t1 subtrans begin insert into t2 values (3) update t3 set col = 3 subtrans end commit BOTH inserts into t1 will be committed, only 1 insert into t2 and one update (both with value 3) will be committed in the end. if you think that the subtrans is in a stored proc (input-value 2 resp. 3) then NOT the whole transaction (the insert into t1) is influenced by the procedure-behaviour, only the work done by the proc itself. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
