Re: [Drizzle-discuss] Improving the Engine API (was Re: New PBXT Drizzle-specific storage engine...)

Fri, 04 Dec 2009 06:01:44 -0800 

Hi Roland,

On Dec 4, 2009, at 1:08 PM, Roland Bouman wrote:


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>
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: 

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




_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to     : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help   : https://help.launchpad.net/ListHelp

Reply via email to