Bear with me, this is confusing to explain.

I noticed something on Apache::DBI (translated, i lost 2days figuring out what was going on by trailing mysql query logs , and watching DBI with a tracelevel )

I'm not sure what's causing this: apache:dbi or dbi , or even mysql being stupid

there was a bit of user error in it as well, i was catching errors not all too well. but anyways, here's what happens:

in the following log from the mysql query log, 427 is a mysql connect id that has: { 'RaiseError' => 0, 'AutoCommit' => 1 , 'TraceLevel' => 1 }; 427 Query set autocommit=1 (original connection - dbi requires you to have autocommit on to call begin work, rollback,etc - which i need outlined in my logic as i share code w/python/php people who expect to see that. 427 Query set autocommit=0 (i believe this is from the begin work in dbi
                 427 Query       SELECT XXX     
                 427 Query       UPDATE XXX     
                 427 Query       INSERT INTO XXX        
                 427 Query       rollback       
427 Query set autocommit=1 ( restores connection on commit / rollback

Now. If you connected to mysql DBI and have RaiseError => 1 , and you died in that transaction before rollback was called... your transaction doesn't end MySQL will stayed locked up

If you hit reload (or visit another url) and get a different Apache::Child to serve your request, nothing is perceptable

Until... You hit the same ApacheChild using your server and request the same DB handle that is cached by Apache DBI

Then you curse a lot

Because, I'm not exactly sure why, you'll have something like this going on (take straight from my query log)

Connection 1    
                  427 Query       set autocommit=1      
                  427 Query       set autocommit=0      
                  427 Query       SELECT XXX    
                  427 Query       UPDATE XXX
Raise Error dies before you call a rollback
Connection 2
                  427 Query       set autocommit=1      
                  427 Query       set autocommit=0      
                  427 Query       SELECT XXX    

Did that just do what i think it did? oh yes, the toggle on autocommit by connection 2 commits the failed transaction on Connection 1 because a rollback never happened.

Now I found this out because I trapped a RaiseError wrong.

This shouldn't be an issue in a non-ApacheDBI situation, because you'd die on the RaiseError and never commit. the db should pick that up and just fail. but the connection caching recycles the handle and does an implicit commit with the toggle.

So, I'd like to offer the following suggestions:

1 - Someone who understands this better than I - note this in the Apache::DBI documentation. Probably in this paragraph:

=current_pod
Transactions: a standard DBI script will automatically perform a rollback whenever the script exits. In the case of persistent database connections, the database handle will not be destroyed and hence no automatic rollback occurs. At a first glance it seems even to be possible, to handle a transaction over multiple requests. But this should be avoided, because different requests are handled by different servers and a server does not know the state of a specific transaction which has been started by another server. In general it is good practice to perform an explicit commit or rollback at the end of every script. In order to avoid inconsistencies in the database in case AutoCommit is off and the script finishes without an explicit rollback, the Apache::DBI module uses a PerlCleanupHandler to issue a rollback at the end of every request. Note, that this CleanupHandler will only be used, if the initial data_source sets AutoCommit = 0. It will not be used, if AutoCommit will be turned off, after the connect has been done.
=cut

2 - would it be possible to catch the die on a RaiseError, and have a setting to issue an explicit rollback if wanted? i'm not too familiar with dbi - but i'd love something like that

Anyways, I hope my experience incorrectly handling the RaiseError will keep the next person from trailing logs all day.


Reply via email to