perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Vanasco <>
Subject Apache::DBI question
Date Sat, 14 Jan 2006 02:13:38 GMT
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 /  

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:

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.

	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.

View raw message