Unexpected Transaction Commit in DBI / Apache::DBI

From: Mark Edwards (Mark.Edwards_at_jet.uk)
Date: 01/24/05 

To: "'dbi-users@perl.org'" <dbi-users@perl.org>
Date: Mon, 24 Jan 2005 08:13:37 -0000

I have been experiencing some problems with the following configuration:

Solaris 5.7 on a Sun Ultra-250, running Apache 2.0.48, mod_perl 1.9913
(built as DSO)

I'm using the following perl modules:

Perl 5.8.0
DBI 1.46
DBD::ODBC 1.13
Apache::DBI 0.94

The RDBMS is Mimer 8.2.5G (www.mimer.com), connecting via unixODBC 2.2.0.

I get an unexpected transaction commit in the following example script:

$dbh = DBI->connect('dbi:ODBC:mydb','myuser','mypass', { AutoCommit => 0 })
or die $DBI::errstr;
$sth=$dbh->prepare("insert into mytable (mycol) select max(mycol)+1 from
mytable") or die $dbh->errstr;
$sth->execute or die $sth->errstr;
# the insert statement above is (erroneously?) committed as soon as the
following connection initiates:
$dbh = DBI->connect('dbi:ODBC:mydb','myuser','mypass', { AutoCommit => 0 })
or die $DBI::errstr;
$sth=$dbh->prepare("insert into mytable (mycol) select max(mycol)+1 from
mytable") or die $dbh->errstr;
$sth->execute or die $sth->errstr;
$dbh->rollback or die $dbh->errstr;

My expectation for the second connect statement would be that Apache::DBI
would simply return a handle to the already open connection from the first
connect. Since AutoCommit is off, the final rollback would prevent both
inserts. This was how things used to work before a substantial upgrade from
a very old version of Apache (running Mod Perl "1").

Not knowing which of the modules things were going wrong in, I hacked all of
them, eventually using DBI tracing and some of my own diagnostic prints and
sleeps in DBI.pm to determine the problem location. It appears to take
place on setting $dbh->{AutoCommit} in the connect subroutine of DBI.pm.

So, I've patched the DBI module with the following 'fix' in "sub connect"
("+" lines added):

+#################################################
if (%$attr) {

    DBI::_rebless_dbtype_subclass($dbh, $rebless_class||$class, delete
$attr->{DbTypeSubclass}, $attr)
        if $attr->{DbTypeSubclass};

+ if (exists $attr->{AutoCommit}) {
+ if ((!exists $dbh->{AutoCommit}) || ($dbh->{AutoCommit} !=
$attr->{AutoCommit})) {
+ $dbh->{AutoCommit} = delete $attr->{AutoCommit};
+ } else {
+ delete $attr->{AutoCommit};
+ }
+ }

    my $a;
    foreach $a (qw(RaiseError PrintError AutoCommit)) { # do these first
        next unless exists $attr->{$a};
        $dbh->{$a} = delete $attr->{$a};
    }
    foreach $a (keys %$attr) {
        eval { $dbh->{$a} = $attr->{$a} } or $@ && warn $@;
    }
}
+#################################################

Now $dbh->{AutoCommit} only gets set if it doesn't already exist or its
value (copied from $attr) has changed.

This patch immediately fixed the example script and appears to have fixed
the problem in general. I'm not 100% convinced there have been no side
effects.

Has anyone else experienced this kind of problem? Is the above patch a
reasonable thing to do?

Thanks
Mark

Relevant Pages

  • Re: DBI query
    ... declare your variables (as local to the first usage as possible) ... manual of DBI. ... $sth->executeor die $DBI::errstr; ... - at this place in the code, not outside of the foreach loop. ...
    (perl.beginners)
  • mysql problem
    ... I am retrieving some data from a syslog mysql database: ... or die DBI->errstr; ... chomp $datetime; ... perldoc DBI etc. ...
    (perl.dbi.users)
  • Re: DBD::ODBC, bind_param_inout and MS SQL stored procedure return statuses
    ... use strict; ... use DBI; ... }) or die; ... -- Terry Pratchett in Sourcery ...
    (perl.dbi.users)
  • perl query, please clear my doubt.
    ... my $sthCmd = $dbh->prepareor die; ... $sthCmd->execute or die; ... I presume that the installation of DBI has not been done properly, can you kindly provide me with the exact installation procedure of DBI on windows or any other suggestion that you think could solve the problem. ...
    (perl.dbi.users)
  • Re: Unexpected Transaction Commit in DBI / Apache::DBI
    ... > I have been experiencing some problems with the following configuration: ... > I'm using the following perl modules: ... > DBI 1.46 ... > This patch immediately fixed the example script and appears to have fixed ...
    (perl.dbi.users)