At 6:05 PM -0600 4/10/07, Dennis Cote wrote:
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. :-)
I wasn't speaking in absolutes there, only in the context that if
child transactions were implemented, and there were 2 ways to do it,
then which way might have the least impact would be the way that most
closely resembles SQLite's current behaviour.
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.
Good, then this is consistent with what I think should happen.
A transaction is just another name for a unit of work that is atomic,
and each individual SQL statement *is* a transaction.
Therefore, SQLite right now already supports nested transactions to a
limited degree, either 1 level (a SQL statement by itself) or 2
levels (a SQL statement within an explicit larger transaction). I
propose it extend this to N levels.
If you have 2 layers of explicit transactions, and the child
transaction rolls back, and the parent one retains its changes prior
to when the child started, then this behaviour is identical to a
partially completed SQL statement rolling back on failure to the
beginning of that statement, and the results of prior executed
statements remaining.
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.
The explanation to this is simple, really.
Proper child transaction support, where rollback of a child does not
rollback the parent, is needed to deal with the reality that SQL
statements are recursive, and any given SQL statement is itself
defined in terms of other SQL statements, to arbitrary levels, and a
SQL statement needs to be atomic no matter what level of abstraction
you are at.
This is best illustrated with a stored procedure. A stored procedure
is a routine that contains one or more SQL statements. Moreover, the
invocation of said stored procedure is also a SQL statement.
Therefore, both the individual SQL statements in the procedure plus
the entire invocation of the stored procedure each need to be atomic,
and hence a transaction.
If only the SQL statements inside the procedure were atomic, and the
call to the procedure wasn't, then we can end up with a situation
where, if the procedure fails and aborts part way through completion,
then from the point of view of the users, the single SQL statement
that they wrote (which invoked the procedure) did not complete, but
did not leave the database untouched either, and hence left an
inconsistent state. This violates ACID.
Or another example, say you have a trigger (an implicitly invoked
procedure) defined to happen when a table is mutated
(eg,ins/upd/del). From a user's point of view, everything that
trigger does is part of the SQL statement that they invoked to mutate
the table, and if the whole trigger isn't made atomic, then the
user's ordinary SQL statement was not atomic from their POV.
Or if you want to ignore stored procedures, consider a SQL INSERT
statement that inserts 1 row vs one that inserts multiple rows (some
DBMSs support the latter). Say you want to insert multiple rows as
an atomic unit, and you want to invoke a multi-row INSERT statement,
but the DBMS doesn't provide one, so you have to invoke single-row
INSERT statements instead. From your point of view, all the inserts
combined are conceptually a single statement, that should be atomic
in the same way that normal SQL statements are atomic.
A main advantage of child transaction support is that people can
implement their own "SQL statements" and have them behave like
system-defined ones, including the promise of atomicity.
This is a very real need, that should be supported.
If one wants to still deny that rolling back a child without rolling
back a parent has no practical use, then we might as well not have
built-in SQL statements that are atomic, because that is exactly the
same end result for users.
-- Darren Duncan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
