You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Nicolas MONNET <ni...@monnet.to> on 2000/09/07 16:03:04 UTC
Auto rollback using Apache::DBI
Hi there,
I might get something wrong, but while in non-autocommit, if a script dies
before rollbacking or commiting, looks like the transaction never gets
cancelled until I kill -HUP httpd! Quite a problem ...
Is there any known way to catch this?
Re: Auto rollback using Apache::DBI
Posted by Jeff Horn <je...@intralect.com>.
Yes, I ran into this while I was making a version of Apache::DBI which uses
'reauthenticate' to maintain a single connection per Apache child (per
database) and simply reauthenticate on that connection. It turned out that
I modified what $Idx was composed of and didn't understand why I was not
getting rollbacks when sessions ended without commits.
I too think that the cleanup handler should ALWAYS be pushed and that the
handler itself should check for the AutoCommit status before issuing a
rollback. Should be easy enough to implement.
-- Jeff
----- Original Message -----
From: "Honza Pazdziora" <ad...@informatics.muni.cz>
To: "Nicolas MONNET" <ni...@monnet.to>
Cc: <mo...@apache.org>
Sent: Thursday, September 07, 2000 9:17 AM
Subject: Re: Auto rollback using Apache::DBI
> On Thu, Sep 07, 2000 at 04:03:04PM +0200, Nicolas MONNET wrote:
> >
> > I might get something wrong, but while in non-autocommit, if a script
dies
> > before rollbacking or commiting, looks like the transaction never gets
> > cancelled until I kill -HUP httpd! Quite a problem ...
> >
> > Is there any known way to catch this?
>
> Looking at the code in Apache::DBI 0.87, the handle is only rollbacked
> if the AutoCommit is set to zero during connect, not if you do
>
> $dbh->{'AutoCommit'} = 0;
>
> in your script.
>
> I wonder if the $needCleanup test is wanted at all. We could make it
> a configuration option, not to push the cleanup handler, but I believe
> that generally the rollback is wanted thing in all cases.
>
> --
> ------------------------------------------------------------------------
> Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
> .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain, ...
> ------------------------------------------------------------------------
>
Re: Auto rollback using Apache::DBI
Posted by Honza Pazdziora <ad...@informatics.muni.cz>.
On Thu, Sep 07, 2000 at 04:03:04PM +0200, Nicolas MONNET wrote:
>
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Looking at the code in Apache::DBI 0.87, the handle is only rollbacked
if the AutoCommit is set to zero during connect, not if you do
$dbh->{'AutoCommit'} = 0;
in your script.
I wonder if the $needCleanup test is wanted at all. We could make it
a configuration option, not to push the cleanup handler, but I believe
that generally the rollback is wanted thing in all cases.
--
------------------------------------------------------------------------
Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain, ...
------------------------------------------------------------------------
Re: Auto rollback using Apache::DBI
Posted by Nicolas MONNET <ni...@monnet.to>.
On Fri, 8 Sep 2000, Honza Pazdziora wrote:
|The code
|
| my $needCleanup = ($Idx =~ /AutoCommit[^\d]+0/) ? 1 : 0;
| if(!$Rollback{$Idx} and $needCleanup and Apache->can('push_handlers')) {
| print STDERR "$prefix push PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
| Apache->push_handlers("PerlCleanupHandler", \&cleanup);
|
|of Apache::DBI line 90 suggests that if AutoCommit isn't zero upon
|_connect_, the cleanup won't even be called. So if you do
|
| my $dbh = DBI->connect('dbi:Oracle:sid');
| $dbh->{'AutoCommit'} = 0;
|
|such a $dbh won't be rollbacked.
I did the AutoCommit upon connect. I don't manage to reproduce the error
condition, though. That's a problem.
Re: Auto rollback using Apache::DBI
Posted by Honza Pazdziora <ad...@informatics.muni.cz>.
On Thu, Sep 07, 2000 at 11:06:00AM -0700, Perrin Harkins wrote:
> On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> > |Well, Apache::DBI does push a cleanup handler that does a rollback if
> > |auto-commit is off. Are you saying this isn't working?
> >
> > I've run into a situation where it was'nt. I wanted to make sure
> > it's not the desired behaviour, before I can dig more into it to look how
> > it's heppening.
>
> With AutoCommit off, you should definitely get a rollback on every
> request, provided you actually called DBI->connect on that request. Turn
> on the debug flag ($Apache::DBI::DEBUG = 2) and see if the cleanup handler
> is being run or not.
The code
my $needCleanup = ($Idx =~ /AutoCommit[^\d]+0/) ? 1 : 0;
if(!$Rollback{$Idx} and $needCleanup and Apache->can('push_handlers')) {
print STDERR "$prefix push PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
Apache->push_handlers("PerlCleanupHandler", \&cleanup);
of Apache::DBI line 90 suggests that if AutoCommit isn't zero upon
_connect_, the cleanup won't even be called. So if you do
my $dbh = DBI->connect('dbi:Oracle:sid');
$dbh->{'AutoCommit'} = 0;
such a $dbh won't be rollbacked.
--
------------------------------------------------------------------------
Honza Pazdziora | adelton@fi.muni.cz | http://www.fi.muni.cz/~adelton/
.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain, ...
------------------------------------------------------------------------
Re: Auto rollback using Apache::DBI
Posted by Perrin Harkins <pe...@primenet.com>.
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> |Well, Apache::DBI does push a cleanup handler that does a rollback if
> |auto-commit is off. Are you saying this isn't working?
>
> I've run into a situation where it was'nt. I wanted to make sure
> it's not the desired behaviour, before I can dig more into it to look how
> it's heppening.
With AutoCommit off, you should definitely get a rollback on every
request, provided you actually called DBI->connect on that request. Turn
on the debug flag ($Apache::DBI::DEBUG = 2) and see if the cleanup handler
is being run or not.
- Perrin
Re: Auto rollback using Apache::DBI
Posted by Nicolas MONNET <ni...@monnet.to>.
On Thu, 7 Sep 2000, Perrin Harkins wrote:
|On Thu, 7 Sep 2000, Nicolas MONNET wrote:
|> I might get something wrong, but while in non-autocommit, if a script dies
|> before rollbacking or commiting, looks like the transaction never gets
|> cancelled until I kill -HUP httpd! Quite a problem ...
|>
|> Is there any known way to catch this?
|
|Well, Apache::DBI does push a cleanup handler that does a rollback if
|auto-commit is off. Are you saying this isn't working?
I've run into a situation where it was'nt. I wanted to make sure
it's not the desired behaviour, before I can dig more into it to look how
it's heppening.
Re: Auto rollback using Apache::DBI
Posted by Perrin Harkins <pe...@primenet.com>.
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Well, Apache::DBI does push a cleanup handler that does a rollback if
auto-commit is off. Are you saying this isn't working?
- Perrin
Re: Auto rollback using Apache::DBI
Posted by Matt Sergeant <ma...@sergeant.org>.
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
>
>
> Hi there,
>
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Yes, use some exception handling. That way, the only way your script will
ever not end up in the exception trap is if your httpd seg faults, in
which case the connection will die and the database will roll back the
transaction for you.
See the guide/perl.html for more details.
--
<Matt/>
Fastnet Software Ltd. High Performance Web Specialists
Providing mod_perl, XML, Sybase and Oracle solutions
Email for training and consultancy availability.
http://sergeant.org | AxKit: http://axkit.org