On Sep 29, 2009, at 2:25 PM, Darren Duncan wrote:
1. The basic idea is that we have nested transactions, and starting a child is defining a subunit that needs to succeed or be a no-op as a whole.
I agree in principal; DBIx::Class has this feature, and it's fairly nice. I'm borrowing it for my own project, which is what led me to send my post. I don't think there's a place for it in the DBI, however, since the DBI offers no other features that work in a nested manner.
2. DBI is always in autocommit mode by default, because that treats each SQL statement as an innermost nested transaction of its own. There should not be an autocommit=0 in the interest of consistency.
Are you saying that a transaction not be required for savepoints? I'm pretty sure that a transaction is required for savepoints. Perhaps a call to savepoint() implicitly starts a transaction; not sure if that's a good idea.
3. A slightly higher level of abstraction would provide the greatest user-friendliness, and I strongly prefer the idea of sub- transactions being tied to a lexical scope or block, such as a try- block. So for example, entering a sub-transaction block starts a child transaction, exiting one normally commits that child, and exiting abnormally due to a thrown exception rolls back the child.
I do, too, but the DBI probably should not work that way. You should be able to build that from the DBI.
Making things scope-tied is the safest and easiest to use because users don't have to explicitly call commit/rollback for every begin, similar to how automatic memory management helps us not need to remember to do a 'free' for each 'malloc' in spite of the many ways a code block might be exited. In this situation, there would not be any explicit begin()/commit()/rollback() methods, and also the SQL itself can't call those unpaired. As for implementation, well I think there is a Perl module that implements sentinel objects or whatever they're called, which could be looked at for ideas.
The reason I wouldn't want block-scoped subtransaction support in the DBI is because its current transaction interface does not work that way. Maybe it makes sense for transactions and savepoints to be implemented with block scoping in DBI 2, but not, I suspect, DBI 1.
4. Less ideal for users, but perhaps closer to bare metal or what people are used to, DBI can keep its existing start/begin()/commit()/ rollback() methods, and they just get reused for child transactions. There should be a transaction nesting level counter which DBI exposes with a getter method. When a connection starts, the level is 0. Starting a transaction increments this by 1, and ending (commit or rollback) decrements it; decrementing it below zero is an error. The start/begin() method starts a new child transaction, or a first transaction if there are none, and commit()/rollback() ends the innermost transaction.
Transactions <> Savepoints. This would be a mistake.
This all said, if you still want to have actual named savepoints, well David's proposal sounds fairly decent.
And lets folks like you and me build just what you describe on top of it, quite easily.
Best, David
