Hi Paul, On Fri, Dec 4, 2009 at 3:01 PM, Paul McCullagh <[email protected]> wrote: > Sorry, I meant RELEASE SAVEPOINT. For me it is all the same, but I am > causing more confusion...! > > What I mean is this: > > COMMIT within a transaction means RELEASE! > > A true COMMIT within a transaction is not possible. And it does not make > sense, as you illustrated with your example:
ok - all clear then - thanks! I guess one can always disagree about what to call it then, but to me the assymmetry (savepoints are nested with regard to rollbacks, but not with regard to commits) keeps me from thinking of the whole concept as "nested transactions". Anyway, thanks for the discussion and explanations, much appreciated. kind regards, Roland > > On Dec 4, 2009, at 9:42 AM, Roland Bouman wrote: > >> then it's still feasible: #2 is rollead back, but #1 and #3 can still >> be committed. >> >> But what if we are to do this: >> >> begin >> -- some changes here #1 >> begin >> -- some changes here #2 >> commit >> --- some changes here #3 >> rollback >> >> What would be the semantics of here? > > > A nested transaction that is "committed" is not really committed, it is > released! > > So change #2 are rolled back when the entire transaction is rolled back. > >> I wasn't aware it existed, and AFAICS, the (2003) SQL standard doesn't >> define it. In fact it says: >> >> 16.6 commit statement: >> >> <commit statement> ::= COMMIT [ WORK ] [ AND [ NO ] CHAIN ] >> >> General Rules: >> ... >> 7) All savepoint levels are destroyed and a new savepoint level is >> established. >> NOTE 415 — Destroying a savepoint level destroys all existing >> savepoints that are established at that level. >> >> So I take this to mean that whenever a transaction has begun, the >> first commit occurring after it will commit everything up to that >> point from the start of the transaction - not starting from the point >> of any savepoint set during the transaction. >> >> If it would be possible to specify a savepoint when comitting, I am >> wondering how something like this would be handled: >> >> create table parent(id int primary key); >> create table child(parent_id int foreign key references parent(id)); >> >> start transaction; >> savepoint p; >> insert into parent values (1); >> savepoint c; >> insert into child values (1); >> commit c; >> rollback to savepoint p; >> commit; >> >> Clearly (?), the the insert into child would violate the foreign key >> in case the insert into p was rolled back, so the only way for this to >> be consistent would be if the rollback to savepoint p would somehow >> undo the commit of savepoint c. >> >> Is there any RDBMS out there that supports this? It would surprise me. >> >> >> kind regards, >> >> Roland >> >> >> >> >> >> >>> >>> There is no way to commit savepoint B, if savepoint A has just been >>> rolled >>> back (because all changes in B are included in A). >>> >>> So in order to make sense, savepoint B must be concluded (commit or >>> rollback) before A is concluded. In other words: savepoints must be >>> nested. >>> >>> So savepoints are nothing more than named nested transactions. >>> >>> On Dec 4, 2009, at 9:42 AM, Roland Bouman wrote: >>> >>>> Hi Stewert, all, >>>> >>>> On Fri, Dec 4, 2009 at 12:42 AM, Stewart Smith >>>> <[email protected]> >>>> wrote: >>>>> >>>>> We should also think about savepoints (i.e. nested transactions) >>>> >>>> heh, I always wondered what that means, nested transaction. I mean, I >>>> think I understand savepoints (ability to rollback to a prior bookmark >>>> set in the -single - transaction scope). When I hear nested, I am >>>> thinking of units that exist as a unit inside their a container. >>>> >>>> Now, if a transaction is demarcated by "begin" and terminated by >>>> either rollback or commit, and you're talking about nesting these >>>> things, I am thinking of doing something like: >>>> >>>> begin >>>> -- some changes here #1 >>>> begin >>>> -- some changes here #2 >>>> commit >>>> --- some changes here #3 >>>> commit >>>> >>>> #1, #2 and #3 would be committed. All hunky dory. Now if we did: >>>> >>>> begin >>>> -- some changes here #1 >>>> begin >>>> -- some changes here #2 >>>> rollback >>>> --- some changes here #3 >>>> commit >>>> >>>> then it's still feasible: #2 is rollead back, but #1 and #3 can still >>>> be committed. >>>> >>>> But what if we are to do this: >>>> >>>> begin >>>> -- some changes here #1 >>>> begin >>>> -- some changes here #2 >>>> commit >>>> --- some changes here #3 >>>> rollback >>>> >>>> What would be the semantics of here? >>>> >>>> You could argue that since the outer transaction was rolled back, all >>>> must be rolled back. But that in my mind conflicts with #2 already >>>> being committed. I mean, it's still a commit, right? Or are some >>>> commits more equal than others? >>>> You could also argue that #1 and #3 must be rolled back, and #2 must >>>> be committed, but that can't make sense either, because maintaining >>>> integrity while committing #2 may depend on ability to commit #1, so >>>> this clearly can't be allowed. >>>> >>>> My conclusion is that the term "nested transactions" doesn't make >>>> sense, at least not when nesting is understood as having "transactions >>>> inside transactions" while maintaining an absolute sense of "commit" >>>> >>>> Of course, savepoints do make sense, but there it works because you >>>> simply allow rollback to certain points within the transaction. But >>>> the key here is I guess that in that case, you are always maintaining >>>> exactly one transaction at all times, you just have the ability to set >>>> "bookmarks" for rollback. >>>> >>>> regards, >>>> >>>> Roland. >>>> >>>>> >>>>> -- >>>>> Stewart Smith >>>>> >>>>> _______________________________________________ >>>>> Mailing list: https://launchpad.net/~drizzle-discuss >>>>> Post to : [email protected] >>>>> Unsubscribe : https://launchpad.net/~drizzle-discuss >>>>> More help : https://help.launchpad.net/ListHelp >>>>> >>>> >>>> >>>> >>>> -- >>>> Roland Bouman >>>> http://rpbouman.blogspot.com/ >>>> >>>> Author of "Pentaho Solutions: Business Intelligence and Data >>>> Warehousing with Pentaho and MySQL", >>>> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html >>>> >>>> _______________________________________________ >>>> Mailing list: https://launchpad.net/~drizzle-discuss >>>> Post to : [email protected] >>>> Unsubscribe : https://launchpad.net/~drizzle-discuss >>>> More help : https://help.launchpad.net/ListHelp >>> >>> >>> >>> -- >>> Paul McCullagh >>> PrimeBase Technologies >>> www.primebase.org >>> www.blobstreaming.org >>> pbxt.blogspot.com >>> >>> >>> >>> >> >> >> >> -- >> Roland Bouman >> http://rpbouman.blogspot.com/ >> >> Author of "Pentaho Solutions: Business Intelligence and Data >> Warehousing with Pentaho and MySQL", >> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html > > > > -- > Paul McCullagh > PrimeBase Technologies > www.primebase.org > www.blobstreaming.org > pbxt.blogspot.com > > > > -- Roland Bouman http://rpbouman.blogspot.com/ Author of "Pentaho Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL", http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470484322.html _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
