At 12:33 PM -0400 4/9/07, Igor Tandetnik wrote:
Dennis Cote <[EMAIL PROTECTED]> wrote:
Darren Duncan wrote:
I will clarify that child transactions are just an elegant way of
partitioning a larger task, and that parent transactions always
overrule children; even if a child transaction commits successfully,
a rollback of its parent means there are no lasting changes.
Because of this, and the fact that a transaction is basically a
guarantee that all or none of the enclosed statements are executed, it
is much simpler to implement nested transactions using a counter and
the existing transaction API in a set of wrapper functions. There is no
need to maintain all the intermediate state information.
Support for nested transactions should allow one to roll back the
child transaction to the point where it has started, but still
commit the overall transaction. Consider:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
ROLLBACK child;
insert into t values ('c');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'c', but not 'b'. As far as I can tell, this is
impossible to implement in terms of existing SQLite API.
Yes, exactly.
The whole point of nested transactions is that if a child transaction
fails, the state still retains all changes made prior to the child
starting, and after its parent started. It is a lot less
complicated, and particularly more reliable, for the DBMS itself to
manage this.
Moreover, in the case of stored procedures like triggers where
triggers have their own child transactions, the application doesn't
even see control between different transaction levels.
The whole point of being implemented as separate transactions rather
than "save points" is that any particular block of code or SQL that
needs to be atomic doesn't have to special case how it is defined
depending on whether it is a main or child transaction, and it
doesn't matter how many parents it has ... just like a function
invocation in a normal programming language doesn't have to know how
high the call stack is.
Note that, if SQLite currently supports the ability to have an
individual SQL statement fail and leave no lasting effects, without
any non-nested transaction rolling back, then it is already partially
supporting the nested transaction feature, such that there are up to
2 transaction levels, any explicit one, plus the implicit one that is
SQL statement itself. So we just have to extend this to N levels.
Now, as far as user syntax goes ... considering my 3rd paragraph
above, it is vital that the syntax for start/commit/rollback a child
transaction is identical to invoking an initial/non-nested
transaction ... so that users don't have to know whether they are
nested or not.
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
