Darren Duncan wrote:
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.
Of course not. A reasonable minimum is to do nothing and leave SQLite as
is. :-)
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.
An SQL statement is atomic. It either completes or does nothing. If it
fails it returns an error to the application.
It is up to the application to decide if an error warrants rolling back
a transaction. The database never does that on its own. The application
must execute a ROLLBACK statement. If a transaction is rolled back, it
is done in an atomic fashion. The database is left in the same state it
was before the transaction started. If you don't do this you have
eliminated the atomicity property of the transaction.
The A in ACID is for atomicity. In Transaction Processing: Concepts and
Techniques, Jim Gary and Andreas Reuter describe the atomicity property
of a transaction as:
Atomicity: The transaction consists of a collection of actions. The
system provides the all-or-nothing illusion that either all these
operations are performed or none of them are performed; the transaction
either commits or aborts.
Regardless, I like to think of SQL statement failures like exceptions,
But they are not. If you want, you can throw an exception when an error
is reported to your application. When you catch the exception you can
commit or roll back the transaction as appropriate.
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.
This is what a SAVEPOINT is for SQL:1999, PostgreSQL, Oracle, and many
other standard databases. Using them, you can roll back to a savepoint
rather than to the beginning of the transaction by specifying the
savepoint to return to in your rollback statement. This is the standard
way to get the effect you are looking for with nested transactions that
can be rolled back. In fact, savepoints can be implemented with nested
transactions.
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.
Reasoning by analogy is always dangerous, but committing changes to a
database atomically is *nothing* like browsing the web.
If you have a real example of an database application that needs to
commit a part of a transaction, I'm fairly sure the application can
split that optional part into a separate transaction. I mean, if that
action can fail and the rest of the transaction can still be said to be
complete, then there is no need to perform that action at that time
along with the other actions in the transaction. It can be done later,
and either succeed or fail on its own, because obviously it didn't
matter if it succeeded or failed in the first place.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
