Skip Menu |

This queue is for tickets about the Apache-DBI CPAN distribution.

Report information
The Basics
Id: 38742
Status: open
Priority: 0/
Queue: Apache-DBI

Owner: Nobody in particular
Requestors: earl [...]

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

Subject: Apache::DBI rolling back transactions globally
Date: Tue, 26 Aug 2008 16:28:51 -0700
To: bug-Apache-DBI [...]
From: Earl Ruby <earl [...]>
Download (untitled) / with headers
text/plain 2.3k
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 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
Download apache_dbi_test
text/plain 2k
#!/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 = ''; 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;
Download apache_test.sql
text/x-sql 648b
-- 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; 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;
Subject: Re: [ #38742] AutoReply: Apache::DBI rolling back transactions globally
Date: Mon, 08 Sep 2008 16:13:47 -0700
To: bug-Apache-DBI [...]
From: Earl Ruby <earl [...]>
Download (untitled) / with headers
text/plain 1.7k
I found a problem in v1.07. On line 275 in the reset_startup_state method the first thing the method does before handing out a connection is to rollback the connection globally. That means that all transactions currently in progress which use this connection will be rolled back. This is a bad idea. In earlier versions of the handle would only be rolled back once, in PerlCleanupHandler, after all connections had disconnected. The entire reason why PerlCleanupHandler goes to great lengths to track when the last connection disconnects is to avoid this problem. If you remove the "Rollback current transaction if currently in one" section the problem goes away. The way that connection pooling is implemented is still flawed for applications that use transactions (AutoCommit is off). Pooling should track whether a connection is currently active, and that connection should not be used by another process while it's active. A connection could be reused after the process disconnects, but reusing while a transaction is in progress violates transactional integrity. (See Here is a diff showing the lines to remove: # diff -ruN --- 2008-08-26 20:56:44.000000000 +0000 +++ 2008-09-08 23:03:48.000000000 +0000 @@ -272,11 +272,6 @@ sub reset_startup_state { my $Idx = shift; - # Rollback current transaction if currently in one - $Connected{$Idx}->{Active} - and !$Connected{$Idx}->{AutoCommit} - and eval {$Connected{$Idx}->rollback}; - foreach my $key (@attrs) { $Connected{$Idx}->{$key} = $Connected{$Idx}->{private_Apache_DBI}{$key};
From: perrin [...]
Download (untitled) / with headers
text/plain 813b
Hi Earl, I agree that reset_startup_state should not be called when handles are given out. It would make more sense to call that in cleanup after the rollback. If you'd like to try making a patch, I can submit it to the dev list. I think you may be misunderstanding the purpose of Apache::DBI a little bit. It isn't intended to pool connections, but rather to keep connections persistent. It does this in a fairly magical way in order to help with adapting existing CGI scripts. It is possible to open a non-persistent handle, or just get a different handle by varying the connect() parameters. This has been discussed on the mod_perl list. Since you aren't adapting a CGI script and you seem to need finer control, you might consider switching to DBI->connect_cached() in place of Apache::DBI. - Perrin

This service is sponsored and maintained by Best Practical Solutions and runs on infrastructure.

Please report any issues with to