At 3:43 PM -0700 4/5/07, <[EMAIL PROTECTED]> wrote:
I have just read the omitted features section and noticed that SQLite doesn't support nested transactions.
Which is *very* unfortunate indeed. It would be *so* much easier for users to get atomicity right at multiple levels if nested transactions were supported. Eg, individual SQL statements, plus all the statements in a trigger, plus arbitrary parent routines, are each atomic and succeed or fail as a unit. In general, the feature would enable lots of other features with little additional cost for those.
I will clarify that child transactions are just an elegant way of partitioning a larger task, and that parent transactions always overrule children; even if a child transaction commits successfully, a rollback of its parent means there are no lasting changes. Savepoints are conceptually related, but a lot less elegant, and should be avoided.
What effort is required to add this feature?
I would expect that 99% of the work would be in the pager layer, where transactions are currently implemented, afaik.
Moreover, it should be fully possible for this to be done in a backwards-compatible way, such that no file format changes are necessary, and older versions of SQLite 3 could use the same database files as the newer ones without trouble.
To get this to work would basically involve having additional journal files, with the original one being for the parent-most transaction, and with an additional one for each transaction level, or some such arrangement; the extra ones could have file names like the original but numeric suffixes indicating the transaction level.
Note that each individual SQL statement is itself an implicit child transaction of whatever level we're otherwise in (or it would be the parent-most transaction if no other parents exist, which is the default situation), assuming SQL statements are atomic, and likewise, so would all the trigger invocations resulting from a SQL statement would be collectively implicitly atomic.
Starting a child transaction would add a journal file, with the state of any child-changed pages prior to transaction start being written there, and if the child aborts, then that file is rolled back from or committed to the next parent level's file as appropriate.
Note that to maintain backwards compatability, the original journal file will still need all pre-change pages written to it too, but intermediate-level files don't necessarily need this, or it can be done, as the implementer wishes.
What would be the next best DB that does support this feature? Ray
I don't know. -- Darren Duncan ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
