Dennis,
There are many examples that can be solved better with nested
transactions. One of them could be the following:
Imagine one application that can import data from a file. You want that,
in case of computer crash, either all the data of the file is imported or
none. At the same time, you want the user to manually accept or reject
every section of the file.
This example can be modelled in a very natural way with a transaction
covering the full file import and a nested transaction covering every
section.
I am sure that it is easy to find many examples more where nested
transactions can be the most natural way to solve a problem.
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com [EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
En Wed, 11 Apr 2007 23:33:52 +0200, Dennis Cote <[EMAIL PROTECTED]>
escribió:
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]
-----------------------------------------------------------------------------
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com [EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
