[Dbix-class] ROLLBACK seems to be skipped on 0.08

[Darren Duncan]

At 2:08 PM -0500 10/22/07, Brandon Black wrote:
>On 10/22/07, Jesper Krogh <jesper at krogh.cc> wrote:
>>  Brandon Black wrote:
>>  > So I guess what I'm saying is that forcing AutoCommit=>0 users to use
>>  > explicit txns everywhere isn't compatible with the idea of generic
>>  > DBIC modules/plugins and the idea of allowing AutoCommit=>1 users to
>>  > not wrap everything in txns.
>>  >
>>  > I'm still thinking...
>>
>>  Would it be an option to clearly mark the $storage->txn_begin/commit
>>  stuff as deprecated and make it work "as broken" as in 0.07?
>>
>
>Yes, I think, but it's more than just manual txn_begin/commit that are
>broken and should be deprecated...
>
>The 0.07 behavior under AutoCommit=>0 in general is broken too, and we
>shouldn't be aiming to go back to it.  We should be looking for a
>long-term solution first, and then once we've got a workable long-term
>solution, we can examine what the best interim approach is to get us
>there that doesn't conflict and doesn't cause existing code more grief
>than necessary.

In case it would be helpful, I'll briefly describe the behaviour of 
Muldis DB (whose language is Muldis D rather than SQL) with respect 
to transactions.

If you end up adopting Muldis DB for a DBIx-Class Storage:: layer, 
you would have to account for that in your own interface, or 
alternately in just your implementation, since it would be no more 
complex to emulate the current DBIx-Class behaviour over it relative 
to the complexity of doing what you do now over DBI; but if your 
interface is the same, that is less system complexity.

Or if you don't adopt Muldis DB, hopefully you will learn from this 
design anyway.

Muldis DB's API is based on the concept of invoking stored routines, 
via call_proc(), those routines being either/both system-defined or 
user-defined.  You should find it the simplest and most efficient to 
use if you have, at least conceptually, wrapped your data 
manipulation statements in stored procedures (that have 0..N 
parameters) and those are part of your database schema, and your 
application just invokes those rather than directly invoking 
insert/update/delete.

By default, each variable update operation in Muldis DB is atomic, 
including database variables; more specifically, every system-defined 
routine that updates a variable is atomic, that is, an implicit 
transaction.  In SQL terms, this includes each insert/update/delete, 
which are system-defined routines.

Muldis DB always works with a nested transaction paradigm, and each 
system-defined routine call is an implicit child-most transaction of 
any explicit parent transaction that there might be.  If there is no 
explicit parent transaction, then effectively the DBMS is operating 
in auto-commit mode, save that each system routine call is atomic. 
The only way to effectively turn off this auto-commit mode is to 
explicitly define a transaction around, directly or indirectly, 
multiple system routine calls.

Within Muldis D routines, all explicit transactions are specifically 
tied to a particular lexical scope, and moreover these are tied to 
the exception-based error handling system.  If the lexical scope in 
question ends normally, then its transaction commits; if it ends 
early due to an exception, then its transaction rolls back.  You 
can't turn exceptions off but you can trap them.

Based on my understanding of DBIx-Class' txn_do(), using that is the 
closest thing to (assuming autocommit=1), and/or identical to, how 
Muldis DB works, and txn_do() is also the type of design I recommend.

Muldis DB also has separate start|commit|rollback_trans() methods 
that the Perl application can invoke (but Muldis D stored routines 
can't) whose purpose is to support wider-scope parent-most 
transactions in the application where it isn't practical to put all 
parts of it into a stored routine, such as because interaction with 
non-database systems or users needs to be done between stages.

Based on my understanding, DBIx-Class' separate 
txn_begin|commit|rollback() routines correspond to this, assuming 
they aren't invoked by user code in a txn_do().

To summarize, I recommend for DBIx-Class an emphasis on txn_do() for 
any situation where it would work, which includes executing just 
single operations, and that autocommit=1 should be constant.  An 
autocommit=0 can be simulated by just having explicit 
begin/commit/rollback where appropriate.

-- Darren Duncan