At 7:56 PM -0400 5/29/06, Rob Kinyon wrote:
All - Would it be possible to change AutoCommit to $dbh->in_transaction() returning the number of nested transactions and a value of 0 meaning "Not in a transaction"? Then, begin_work actually means something besides setting a flag. Then, AutoCommit can just go away. While I understand that setting AutoCommit to 0 is a signal to the DBD to DWIM, I feel that doing things this way will help users think in terms of transactions (unless their database doesn't support them, in which case they won't care). Thanks, Rob
I agree with this idea, though I'll further clarify what I would see as a good solution. Issues of backwards compatability are ignored for simplicity, but can be accommodated with this idea.
First of all, I would see it as ideal if we can have a generic interface support for N levels of nested or child transactions, where N can be >= 0. If any parent transaction is rolled back, it will be as if any committed child transactions never happened. This is related to SQL's concept of savepoints, but that users should never have to know whether there is an active transaction when they start and commit/rollback another one of their own, which makes database using code easier to reuse or move around.
At one end of the conceptual scale, every distinct SQL statement would be atomic and/or an implicit transaction; if a statement fails, it will be as if it was never run (rollback); if it succeeds, then its effects continue to last (commit). One should never need to use an explicit transaction to ensure that a single SQL statement is atomic.
If one explicitly starts a transaction, then the per-statement implicit transactions are children of that, so a rollback or commit of the main transaction says whether the results of any successful statements persist.
Once someone commits an explicit transaction, we're back to each statement becoming a parentmost commit on success.
So explicit transactions indicate when we want a group of statements to be collectively atomic; and multiple levels of explicit transactions indicate wider or narrower groups of atomicity.
When a database connection is first opened, it defaults to each successful SQL statement's results persisting. (AutoCommit=1.)
Note that it would be an error to invoke 'commit' or 'rollback' unless you first have an active explicit transaction begun with 'start'.
Note that if there are any explicit transactions still open when a database connection is to close, that is technically an error, and any such transactions would be rolled back.
For backwards compatability, if one says AutoCommit = 0 at connect() time, it has the same effect as saying a normal connect() plus an explicit 'start'.
-- Darren Duncan
