Hi Paul, On Fri, Dec 4, 2009 at 12:38 PM, Paul McCullagh <[email protected]> wrote: > However, savepoints must also be "nested". For example, the following is not > possible: > > begin > -- some changes here #1 > set savepoint A > -- some changes here #2 > set savepoint B > --- some changes here #3 > rollback savepoint A > commit savepoint B <--- ERROR > commit
Agree. But where did you find the syntax COMMIT <savepoint> ? 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 _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
