Darren Duncan wrote:
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.
Darren,
Yes I did assume no coupling because you didn't suggest any. If there is
coupling this is just another case of the second example.
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.
So put all the sub-steps in a subroutine and call it.
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.
You can always manufacture an example where nested transactions are
useful. My point is that these situations are rare in real world
applications.
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.
Again subroutines solve this issue quite handily.
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.
That is not true and you know it. You are just pushing the complexity
back to Richard. He will have to implement the changes to the parser,
code generation, pager layers, and test suite, as well as address the
backwards compatibility issues. How much more complicated is the nested
transaction solution if *you* have to implement it?
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.
If it is a trivial as you suggest, then you should have already prepared
a patch. :-)
How will nested transactions make creating a your wrapper easier? Please
be specific.
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).
You will have to discuss this with Richard Hipp.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------