| Subject: | Apache::DBI rolling back transactions globally |
| Date: | Tue, 26 Aug 2008 16:28:51 -0700 |
| To: | bug-Apache-DBI [...] rt.cpan.org |
| From: | Earl Ruby <earl [...] switchmanagement.com> |
I'm having an odd problem under Apache::DBI v1.07 connecting to an
Oracle database. This problem does not occur on older servers running
Apache::DBI v0.94, and it does not occur if I remove "PerlModule
Apache::DBI" from my Apache config.
The problem is that DBI->connect is returning the same connection
multiple times to the same process. This means that if there is a
transaction in progress on a connection, and a utility function gets
called which opens it's "own" connection to the database, when the
utility function disconnects it ends up rolling back the transaction for
the other connection.
I've attached a sample CGI script (apache_dbi_test) and an Oracle SQL
script that creates the apache_test table (apache_test.sql) that
demonstrates the problem. The main() subroutine gets a connection,
inserts a record into a table, then calls a utility function.
The utility function opens a connection to the database then
disconnects. Apache::DBI rolls back any pending transactions on this
connection due to the disconnect.
The main() subroutine then selects the maximum record ID again using
it's original connection, only the record is no longer there because the
utility function rolled the transaction back.
The output under Apache::DBI v1.07 looks like this:
---------------------------------------------
Maximum ID is 108, conn=Apache::DBI::db=HASH(0x80b1ac6c)
utility_function conn=Apache::DBI::db=HASH(0x80b1ac6c)
Maximum ID is 83, conn=Apache::DBI::db=HASH(0x80b1ac6c)
The maximum record ID has changed, the connection was rolled back
---------------------------------------------
Under 0.94 it looks like this:
---------------------------------------------
Maximum ID is 109, conn=Apache::DBI::db=HASH(0x9fb9a2c)
utility_function conn=Apache::DBI::db=HASH(0x9fb9a2c)
Maximum ID is 109, conn=Apache::DBI::db=HASH(0x9fb9a2c)
The maximum record ID is the same, the connection was not rolled back
---------------------------------------------
I'm thinking that for connection pooling to work, you'd want to track
which connections are currently active, and only return
currently-inactive connections when a script requests a new connection.
When disconnect is called you'd return a connection to the inactive
pool. I've read through DBI.pm and it doesn't seem to be doing this,
although I may not be understanding it correctly.
--
Earl C. Ruby III, Senior Systems Engineer / Developer
Switch Management
#!/usr/bin/perl
# A test of Apache::DBI connection pooling
use DBI;
use constant DB_USER => 'scott';
use constant DB_PASSWORD => 'tiger';
use constant DB_ATTRIBUTES => { RaiseError => 1, AutoCommit => 0, LongReadLen => 10000000 };
use constant CRLF => "\x0d\x0a";
# Prototypes
sub main;
sub utility_function;
sub get_connection;
main;
sub main {
print 'Content-Type: text/html' . CRLF . CRLF;
my $conn = get_connection;
# Add a record to apache_test
$conn->do("insert into apache_test (name) values (to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'))");
# Get the maximum ID from apache_test
my $stmt = $conn->prepare("select max(id) as max_id from apache_test");
$stmt->execute;
my $row = $stmt->fetchrow_hashref;
print "<p>Maximum ID is $row->{max_id}, conn=$conn\n";
my $max_value = $row->{max_id};
$stmt->finish;
# Call a utility function
utility_function;
# Now see what the maximum ID value is using the same connection
my $stmt2 = $conn->prepare("select max(id) as max_id from apache_test");
$stmt2->execute;
my $row2 = $stmt2->fetchrow_hashref;
print "<p>Maximum ID is $row2->{max_id}, conn=$conn\n";
if ($max_value != $row2->{max_id}) {
print "<p><b>The maximum record ID has changed, the connection was rolled back</b>\n";
} else {
print "<p>The maximum record ID is the same, the connection was not rolled back\n";
}
$stmt2->finish;
$conn->disconnect;
}
sub utility_function {
# Should have no effect on other connections in other subs
my $conn = get_connection;
print "<p>utility_function conn=$conn\n";
$conn->disconnect;
}
sub get_connection {
my $connect_string = 'dbi:Oracle:dev002.switchmanagement.com';
my $conn = DBI->connect($connect_string, DB_USER, DB_PASSWORD, DB_ATTRIBUTES);
# Set up a default date format that gives us full precision
$conn->do("alter session set nls_date_format = 'YYYYMMDDHH24MISS'");
# Return all hashref keys in lowercase
$conn->{FetchHashKeyName} = 'NAME_lc';
return $conn;
}
1;
-- Create an Oracle sequence and a table for apache_dbi_test CGI script
create sequence apache_test_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;
create table apache_test(
id number not null,
name varchar2(200) null
)
monitoring;
create or replace trigger apache_test_insert
before insert on apache_test
for each row
declare
new_id integer;
begin
select apache_test_seq.nextval into new_id from dual;
:new.id := new_id;
end;
/
alter trigger apache_test_insert compile;
insert into apache_test (name) values ('Test record A');
insert into apache_test (name) values ('Test record B');
commit;