Darren Duncan wrote:
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.
Yes, I know. I re-read my last post this morning and it sounded a bit
harsh, which is not what I intended. I'm sorry if I offended.
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.
I understand, but this extension is a; non-standard, and b; unnecessary.
Additional levels can only include additional actions. If those actions
must be completed atomically, then a single transaction is sufficient.
If not, then they don't need to be in the same transaction.
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 executed a series of SQL statements without a transaction
active, the changes due to each statement are committed immediately when
it ends. If you need the series to be atomic, you enclose them in a
transaction. If you haven't enclosed them in a transaction, then you are
implicitly saying you don't require the last one to complete
successfully for the first ones to be considered successful.
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 simply isn't true. SQL statements are not recursive, and SQL
statements are not defined in terms of other SQL statements.
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.
Yes it is, and if the invocation is not included in a larger
transaction, the invocation statement will succeed or fail depending
upon the result of the procedure. If the procedure completes
successfully, the invocation was successful, and the invocation
statement's implicit transaction is committed.
However, if the invocation statement is one part of a larger
transaction, then the changes are not committed until that larger
transaction completes successfully. If the stored procedure fails, then
the invocation statement will return an error. If the invocation
statement fails, then the larger transaction can not be completed
successfully, and so it should be rolled back.
If you think the prior statements in the larger transaction should be
committed even if the stored procedure invocation statement fails, then
you are really saying that the invocation statement should not be part
of the transaction.
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.
No, you are missing the point of a transaction. It's *all-or-nothing*.
The individual statements in the stored procedure are not transactions.
They are statements executed as part of the transaction that is opened
implicitly by the invocation statement, or as parts of a previously
opened transaction. They themselves will never open a transaction
implicitly because one will always be open when they execute, either one
open by the invocation statement, or one that was already open when the
invocation statement is executed.
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.
The same mechanism applies to triggers. They are invoked by other SQL
statements. Those invoking statements either open a transaction, or are
part of a larger transaction.
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.
So then you would open a transaction before the first insert, execute
all your single row inserts, and then commit the transaction. It is the
transaction that makes the series of inserts 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.
You don't implement your own SQL statements, rather you implement sets
or sequences of SQL statements. If your sequence must be atomic to work
correctly, then you enclose them in a transaction.
Often users want to put the code that implements the sequence of SQL
statements into a subroutine that they can call when ever that sequence
is needed. The problem for users is that they cannot open another
transaction when one is already open, and they don't want to prematurely
close one when their routine is used as part of a larger transaction.
This makes it difficult to call the subroutine without knowing the
current transaction context. If there is an open transaction, then it
shouldn't try to open another (because it will generate an error), but
if there is not it should (assuming the set of operations must be
atomic). Similarly, if the subroutine opened the transaction, it should
close it, but it must not close the transaction if it did not open it,
but rather simply executed within it..
These issues are handled correctly by counting the opens and pairing
them off with closes until the first open it reached (which defines the
beginning of the series that must be considered atomic), and the
transaction is actually closed and the changes committed.
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.
That is simply not true either. The atomicity is not a property of the
SQL statement, it is a property of the transaction that the statement
executes in (and they are always executed within a transaction, either
and explicit one, or an implicit one).
I don't think I can say the same thing in many more ways. :-)
I am not saying there is *no* use for nested transactions or savepoints
(which are the standard method of undoing parts of a transaction), just
that they are not really as necessary or useful as most people assume
when they first run into the single transaction issue. For *most*
applications, a single transaction, and a set of functions that help
subroutines do the right thing with respect to opening and closing the
transaction, are all that is needed. I think this level of support is
more in keeping with the minimalist nature of SQLite. Those few
application that actually need that functionality may be better of using
PostgreSQL, Oracle, or some other database engine that provides savepoints.
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
