At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'd', but not 'b' and 'c'.
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think
it is), then the exact same result can always be achieved with the
simpler sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
While it is true in some cases that an application can be written to
know in advance whether certain SQL statements need to be run or not,
there are other cases where it can only easily know after having
tried it.
One type of situation that stands out the most to me is if you have
state constraints defined (in some way) on the database for which it
is only really feasible to calculate the constraint definition after
DML has occurred, because you want SQLite to do the calculation
itself on the post-DML-state and it is technically simpler that way;
if the constraint fails, we would want to be able to just rollback
the DML that caused the state to break, but not the other valid stuff
before that, since we could have more stuff after this attempt that
needs to be atomic with stuff before the attempt.
Well, the key thing in that example, and many situations, is that the
child transaction is doing something that we may or may not want to
rollback and we won't know until after it is tried.
This said, I can also see situations where there is an unconditional
child rollback, and that is where we want SQLite itself to perform a
querying or calculating task using a temporarily changed database
state as input. We want the result of the query, but not the changed
state that went into its input. But there is other changed state
before that which does need to go into the input, and it needs to
persist, and be atomic with other state changes done after the
calculation.
Now, you could say make the application do that work, but the fact is
that certain kinds of data processing are more efficiently done by a
DBMS itself.
All this said, I look forward to rhurst's specific problem being
explained, so it is easier for us to see whether child transaction
support would help *that* problem.
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
