Darren Duncan wrote:
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.
Darren,
You have lost me here. If this transaction is considered successful
without executing the middle step (which is the same as executing it and
then undoing that execution) then that step does not belong in this
transaction.
Instead of
begin
step 1
savepoint after_1
optional step 2
if error
rollback to savepoint after_1
step 3
commit
You can do
begin
step 1
step 3
commit
begin
optional step 2
if error
rollback
else
commit
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.
This *is* an example of an application where a nested transaction or a
savepoint could be useful. However there is a fairly simple workaround
that gets the same result without a nested transaction.
Instead of:
begin
step 1
savepoint after_1
temp step 2
var = query current state
rollback to savepoint after_1
step 3 using var
commit
or
begin
step 1
begin nested
temp step 2
var = query current state
rollback nested
step 3 using var
commit
You could do:
begin
step 1
temp step 2
var = query current state
rollback
begin
step 1
step 3 using var
commit
And in a multiple access system you could use a variation using chained
transactions to ensure that no other process changes the database state
between the two transactions.
begin
step 1
temp step 2
var = query current state
rollback and chain
step 1
step 3 using var
commit
The cost of this approach is repeating the work done in step 1.
Another approach that could be used in some, but definitely not all,
cases is to use additional SQL statements to undo or invert the effects
of step 2.
begin
step 1
temp step 2
var = query current state
inverse step 2
step 3 using var
commit
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.
Likewise.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
