At 3:33 PM -0600 4/11/07, Dennis Cote wrote:
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
Your example assumes no coupling between the different steps, which
is true some times, but in the general case there can be coupling.
That is, the effect that step 3 actually has can be different
depending on whether step 2 is rolled back or not, either because
step 3 is operating on a different database state, or because step 3
contains conditionals that cause different statements to execute
depending on database state that could have been changed by step 2.
So in the general case, step 2 must always be run after step 1 and
before step 3.
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
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
The cost of this approach is repeating the work done in step 1.
While that workaround may be an acceptable solution for some
situations, I see that as overly complicated and difficult in the
general case.
For one thing, it requires the application to keep track of all the
details of what step 1 was, and step 1 could be arbitrarily complex.
Moreover, step 1 could have been expensive, involving a large amount
of data which may have been input from somewhere and can't be
retrieved again nor stored in RAM; the only copy of it is in the
database.
Or even ignoring the last point there is still the complexity,
especially if one used bind variables that were since freed up for
other tasks, since you aren't just keeping a log of SQL strings to
re-run.
I think that a SQLite pager-based mechanism for tracking child
transactions is quite a bit less complicated and more reliable than
using your workaround, since no details have to be remembered but for
the pages that changed.
Now going off on a tangent ...
To address the oft-raised comment that some people make that any
proposed additions or changes to SQLite be opposed in general on the
principal that "it is supposed to be lite", I say this:
It is perfectly in keeping with SQLite's philosophy for us to add
lots of power to it if the cost of adding that power is low.
Low cost meaning that the codebase doesn't need to increase much if
any, the resource efficiency of running SQLite isn't impacted much,
complexity doesn't raise the bug density appreciably, and
particularly, it isn't more difficult for users to use.
Some features, like proper child transactions as I described, are a
situation where users gain a lot of power at very little cost.
Having proper child transactions means it is a lot easier for users
and developers, particularly SQLite extension or wrapper writers such
as myself, to add powerful features to SQLite using programs while
SQLite itself is hardly more complex. Users are saved a lot of work,
and SQLite developers gain next to none.
By contrast, say, supporting named users in the database and
concurrent database writes and stuff like that is indeed way too
complicated for SQLite to have, and I still support SQLite never
adding support for it.
So SQLite with child transactions is only trivially less lite than it
is now, which is still lite.
In fact, I propose moving rollbackable child transaction support to
the top of the todo list, rather than it being in the middle, given
that its presence can make a lot of other todo or wishlist items much
easier to implement, I believe.
And if it will make a difference, I will even make a monetary
donation (as I can afford to) in order to sponsor its development
(though I would like to think that the benefits are compelling on
their own).
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
