Hi, Came accross a bug when when trying to reduce creation of handles on a site.
Currently Apache::DBI makes the assumption that it AutoCommit => 1 then the handle
does not need cleaning up. This is not neccessariliy true as begin_work switches off
the AutoCommit for one transaction.
First time the handle is used AutoCommit=>1 and a transaction is started with
begin_work but the script fails to call either commit or rollback for any reason (ie
dies) causing an incomplete transaction to exist.
Next time Apache::DBI returns the handle it is still mid transaction and but thinks
AutoCommit is on which it is not. You therefor end up with following scripts thinking
every DBI action is auto commited but it is not. Locks develop on the updated tables
which becomes very messy when not running single process mode.
Usually the only way to remove these locks is by killing the Apache process.
On Oracle the test script uses this table in the scott/tiger schema the bug should
apply to any DB that impliments transaction but I have only tested against Oracle :-
create table T (A number(20));
When the test script is run with test.cgi?commit=0 then the final transaction is not
commited :-
SQL> truncate table T;
truncate table T
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
But with test.cgi?commit=1 then the last transactio is committed or then the patch
applied then it rolls back and this works:-
SQL> truncate table T;
Table truncated.
Please find attached the example script and the patch against CVS v1.7.
Thank you
Paddy
#!/usr/bin/perl
use strict;
use CGI;
use DBI;
use Data::Dumper;
my $cgi = new CGI();
print $cgi->header();
# Apache::DBI test code
my $DBI_CONNECT= "dbi:Oracle:host=xx.xx.xx.xx;sid=ssss";
my $dbh = DBI->connect($DBI_CONNECT,'scott','tiger');
my $query1 = 'select A from T';
my $sth = $dbh->prepare($query1);
$sth->execute();
print "<h1>Test Results</h1>\n";
print '<pre>'.Dumper($sth->fetchall_arrayref)."</pre>\n";
my $before = $dbh->{AutoCommit};
$dbh->begin_work();
my $after = $dbh->{AutoCommit};
my $query2 = 'insert into T (A) values (?)';
my $sth2 = $dbh->prepare($query2);
print "<h1>Test Error Trans</h1>\n";
my $time1=time;
print "This should commit $time1\n";
$sth2->execute($time1);
$dbh->commit();
print "<h1>Test Error Trans</h1>\n";
$dbh->begin_work();
my $time2=time+1000000000;
print "This should roll back $time2\n" if (!$cgi->param('commit'));
$sth2->execute($time2);
$dbh->commit() if ($cgi->param('commit'));
1;
--- DBI/DBI.pm 2003-06-10 12:20:06.000000000 +0000
+++ DBI/DBI.pm 2003-06-10 12:42:07.000000000 +0000
@@ -87,9 +87,10 @@
}
# this PerlCleanupHandler is supposed to initiate a rollback after the script has
finished if AutoCommit is off.
- my $needCleanup = ($Idx =~ /AutoCommit[^\d]+0/) ? 1 : 0;
+ # however cleanup can only be determined at end of handle life as begin_work may
have been called to temporarily turn off AutoCommit.
+ #
# TODO - Fix mod_perl 2.0 here
- if(!$Rollback{$Idx} and $needCleanup and Apache->can('push_handlers')) {
+ if(!$Rollback{$Idx} and Apache->can('push_handlers')) {
print STDERR "$prefix push PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
Apache->push_handlers("PerlCleanupHandler", \&cleanup);
# make sure, that the rollback is called only once for every
pgp00000.pgp
Description: PGP signature
