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.
