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

[Louis Erickson]

Sorry to wake up a slightly old thread, but I've just been bit by this in 
an 0.07 to 0.08 upgrade, and found this thread.  (This bit me as soon as I 
started using 0.08.  I just started using it or I'd have asked earlier.)

One difference between what I'm reading here and what I'm seeing in my own 
app is that I always do a txn_begin/txn_commit, and it does not work as 
I'd expect, either.

(It starts at a transaction level of 1, which is incremented by the begin, 
and decremented by the commit, so that it never actually commits to the 
DB.  To either comment out the begin or add an extra commit works around 
it, despite being extremely goofy looking.)

I do have a question regarding txn_do, though, for I don't understand how 
it can work properly with AutoCommit => 1.

On Sun, 21 Oct 2007, Jesper Krogh wrote:

> Ok.. maybe I didnt make it clear. The problem is that we "serialize"
> object into several records (over 20 mostly) and this serialization
> process should be done atomically, so I don't get any "half-objects" in
> the database. It wasn't the individual record I was talking about.
> 
> This dataset is conceptually "inconsistent" to the application if this
> happens. But the database is of course not aware of this.

This is the primary reason I was using manual txn_begin/txn_commit calls, 
and AutoCommit=>0.

> txn_begin/commit and txn_do solves this.

How does txn_do solve this?  This is the part I don't understand.  Perhaps 
one of the experts here can explain it.

Let's consider the following sequence:

txn_begin
	Add one
	add two
	add three
	Fail!
txn_rollback

I don't see a significant difference between txn_do and this case, at 
least for the purposes of my discussion.  If I'm wrong about that, please
let me know why!

With AutoCommit=>0, other connections to the DB will see none of one, two 
or three.  Only when the commit occurs will that entire operation be made 
visible to other database connections.  Since it is rolled back, they are 
never made visible.

(Yes, I have tested this with my database.  I don't know about SQLite or 
mysql, but there are databases that get this right.)

With AutoCommit=>1, one, two and three will exist in the DB, leaving it in 
a state which other running queries may find and use that data.  Even if 
txn_rollback is smart enough to unwind every change it made (I presume it 
is) there will be a window where there is partially defined information 
visible in the database.

At all times the DB maintains relational integrity.  This will be data 
that isn't consistent from the business rules I'm trying to build in the 
application.

What of the case where the app segfaults or the connection fails during or 
before the app-implemented rollback?  With AutoCommit=>1 the DB will never 
get cleaned up.  (Actually, DBIC might be smart enough to handle a 
reconnect, but a segfault or other app shutdown is still a killer.)  With 
AutoCommit=>0, the changes should roll back when the connection is lost.

What am I misunderstanding to think that these are not risks?  (I almost 
can't believe that someone from this group didn't think of this, so I'm 
assuming I missed something.)

One solution would be to drive the multiple-step operations in to stored 
procedures.  That means I'm suddenly incredibly DB specific, whereas right 
now it's all in Perl, using proper transactions.  I'd rather not move to 
SPs if I don't have to.

It may be that DBIC makes all these changes as quickly as possible so that 
the window for error is minimized.  The window is still there, though.  
The solution there is just to not care so much, and let DBIC get on with 
things, which is probably fine for most apps, but I wouldn't want my bank 
or the air traffic control people to do it. If there is such a hole, it 
should be documented so high-risk applications know about it.

As far as trying to solve this for DBIC goes, there may not be a good 
solution.  I just read the long thread from late October, and I do 
understand that it's complicated.

I have a possible suggestion.  I don't think it was proposed before, 
although I might have missed it.

With AutoCommit=>1, do the things it does to make transactions work now 
that seems to be acceptable.

With AutoCommit=>0, add an implicit txn_begin() and txn_end() call, with 
the txn_begin on connect and txn_end on disconnect, forcing a disconnect 
in an END block.  Code which did not use any transactions would still 
work, and people who expected to do their own would still work.  Their 
commit would just be delayed until disconnect.  Make an extra commit do 
the commit, with a warning, so code with no begin works but is fixed 
eventually.  Perhaps add a warning that this mode may be inconsistent, 
error-prone, etc.  Also document this risk.

It might then be possible to add a new flag, something like 
ManualTransactions or ExplicitTransactions or AnalDBA or something to make 
the txn_begin/commit/rollback work at the DB level and let the developer 
do it themselves, if this was something you wanted to even consider 
supporting.  (It would die if AutoCommit=>1, and would suppress the 
warning mentioned above.)

If the use of txn_do/AutoCommit=>1 does address the possible 
inconsistencies across multiple operations, then the support for 
AutoCommit=>0 is really not important.  If it does leave the opportunity 
for other processes to find partial operations in the DB, I think the 
ability to use the database's atomic multi-operation commits is more 
important.

Any explanation of what I might be misunderstanding would be welcome!

-- 
Louis Erickson - lerickson at rdwarf.net - http://www.rdwarf.com/~wwonko/

I am so optimistic about beef prices that I've just leased a pot roast
with an option to buy.