At 11:46 AM -0600 4/10/07, Dennis Cote wrote:
Samuel R. Neff wrote:
I personally would see value in supporting quasi-nested transactions where
they are nested in name only--increment decrement a counter and commit on
last commit, rollback entire transaction on first rollback. This would have
the advantage that the library would support issuing multiple BEGIN
TRANSACTION statements without error.
I often find in my code that I have library routines that want to run in a
transaction and can run in their own transaction or join an existing
transaction. In SQLite I would need to have extra logic in place to detect
the transaction state and only run in a transaction if one is not already in
place.
I say "would" because the SQLite.NET [1] wrapper provides nested
transactions as a counter already, on top of the SQLite library (as long as
I use the connection.BeginTransaction() method and don't issue an explicit
BEGIN TRANSACTION call myself).
I do the same thing in my wrapper for the same reason. I think this
is by far the most common case where users see a need for nested
transactions. It decouples subroutines from their calling context.
This form of nested transactions is exactly what several other
database engines provide. It is simple to implement and does what
the vast majority of users need.
I'm sure there are circumstances that can tolerate only some of the
statements in a transaction being executed, but for most
applications a transaction is *all or nothing*. They don't want to
execute the first and last statement but skip the middle two. It
seems to me any application that can tolerate some statements in a
transaction not executing could factor those statements out into a
separate transaction.
I think that the question of what would be a reasonable minimum to do
now depends on what SQLite's current behaviour is when an individual
SQL statement fails that is part of a larger transaction.
Remember that each SQL statement is conceptually an implicit child
transaction of its own.
If a failing SQL statement causes the entire transaction to
implicitly rollback, then what you describe about any rollback
causing the whole thing to rollback would be consistent.
If a failing SQL statement just results in only that statement not
leaving a trace and other uncommitted statements in a transaction are
still applied subject to be committed, then an explicit rollback must
not affect anything but the immediate child-most transaction, to be
consistent.
Regardless, I like to think of SQL statement failures like
exceptions, and each transaction level can optionally act like a
try-catch-block; a failed statement throws an exception, and each
transaction in turn is rolled back up to said try-catch block, and
anything done prior to the block being entered is not rolled back
automatically.
Now of course, users would then have to explicitly mark places,
perhaps best as an extension to the "begin transaction" statement
that indicates whether a failure within that transaction will
auto-effect any of its parents; its like the "begin" doubles as a
try-block. Or that may not be the best syntax, so alternatives could
be tried.
I will also say for the record that partial rollbacks are useful.
Having all layers rolling back unconditionally is like having a web
browser program that exits if it has trouble loading a web page,
rather than just saying sorry and moving on. Sometimes it is
reasonable to only rollback the "load this page" transaction rather
than the "main()" transaction. Users may want to try again, or try
going to a different page.
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
