This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: mod_perl list <modperl@apache.org>
From: Stas Bekman <stas@stason.org>
Subject: Apache::DBI and transactions
Date: Thu, 14 Dec 2000 20:09:30 +0100 (CET)
I was in the process of updating the Apache::DBI section of the guide with
the notes from the latest version of this package, and there is a new
section about Transactions. Since I use mysql, it doesn't have
transactions so I cannot it's not absolutely clear to me. For example why
the script should perform a rollback at the end? Isn't it only for the
case where the transaction has failed? Anybody?
Here is the section:
=head3 Transactions
A standard perl script using DBI will automatically perform a rollback
whenever the script exits. In the case of persistent database
connections, the database handle will not be destroyed and hence no
automatic rollback occurs. At a first glance it seems even to be
possible, to handle a transaction over multiple requests. But this
should be avoided, because different requests are handled by different
mod_perl processes and a mod_perl process does not know the state of a
specific transaction which has been started by another mod_perl
process.
In general it is good practice to perform an explicit commit or
rollback at the end of every script. In order to avoid
inconsistencies in the database in case C<AutoCommit> is I<Off> and
the script finishes without an explicit rollback, the C<Apache::DBI>
module uses a C<PerlCleanupHandler> to issue a rollback at the end of
every request.
===
To: Stas Bekman <stas@stason.org>
From: Michael Peppler <mpeppler@peppler.org>
Subject: Re: Apache::DBI and transactions
Date: Thu, 14 Dec 2000 11:55:47 -0800 (PST)
Stas Bekman writes:
>
> I was in the process of updating the Apache::DBI section of the guide with
> the notes from the latest version of this package, and there is a new
> section about Transactions. Since I use mysql, it doesn't have
> transactions so I cannot it's not absolutely clear to me. For example why
> the script should perform a rollback at the end? Isn't it only for the
> case where the transaction has failed? Anybody?
The problem is (would be - Apache::DBI does an automatic rollback via
a PerlCleanupHandler) that if your script has started a transaction
but not committed it, then the next script that gets executed using
that particular database connection will continue the same
transaction. This will likely lead to a very bad situation in the
database with transactions spanning various requests, possible
deadlocks, etc.
So for persistent connections it is essential that the transaction
state at the end of each script be "clean" (i.e. either committed or
rolled back) whether the transactions are implicit (i.e. started due
to the AutoCommit flag being off) or explicit (the SQL executed has a
BEGIN TRAN in it somewhere).
===
To: Stas Bekman <stas@stason.org>
From: Chris Nokleberg <chris@sportsrocket.com>
Subject: Re: Apache::DBI and transactions
Date: Thu, 14 Dec 2000 12:04:41 -0800 (PST)
In case your script makes some db changes "by accident"--if you don't do
an explicit rollback or commit at the end, the uncommitted changes will
hang around, and the next request may end up committing those changes
unwittingly. I rollback at both the beginning and the end of all requests,
just to be safe :)
===
To: Stas Bekman <stas@stason.org>
From: "Bruce W. Hoylman" <bhoylma@qwest.com>
Subject: Re: Apache::DBI and transactions
Date: Thu, 14 Dec 2000 13:03:45 -0700
>>>>> "Stas" == Stas Bekman <stas@stason.org> writes:
Stas> In general it is good practice to perform an explicit commit
Stas> or rollback at the end of every script. In order to avoid
Stas> inconsistencies in the database in case C<AutoCommit> is
Stas> I<Off> and the script finishes without an explicit rollback,
Stas> the C<Apache::DBI> module uses a C<PerlCleanupHandler> to
Stas> issue a rollback at the end of every request.
I believe this paragraph spells it out, to my satisfaction anyway. If a
script exits without committing (or rolling back) a transaction that is
currently in progress, the database remains in the uncommitted state.
Thus any data modified by the script is not actually fully modified,
just ready to be modified. Subsequent requests to the database would
return inconsistent views of this data, i.e. views of the data prior to
the transaction, but not necessarilly what the data will look like once
the transaction is committed.
The reasonable thing to do then, not knowing why a transaction might
still open at this point in the logic flow, is to clean up in a
non-destructive fashion when a script exists. If for example
transaction still is open when a script exits, it is either a result of
programmer, design or logic error, or something unexpected has occurred
during the course of script execution which was not properly handled.
Therefore instead of committing the transaction that contains
potentially unknown consequences, get rid of it and put things back the
way they were.
In this case the idiom appears to be "better to do nothing now than to
try and later undo something that was done unexpectedly", and I guess I
would agree in this case.
Interesting behavior ... I did not know Apache::DBI did this!
===
To: mod_perl list <modperl@apache.org>
From: Stas Bekman <stas@stason.org>
Subject: Re: Apache::DBI and transactions
Date: Fri, 15 Dec 2000 01:49:24 +0100 (CET)
On Thu, 14 Dec 2000, Chris Nokleberg wrote:
> In case your script makes some db changes "by accident"--if you don't do
> an explicit rollback or commit at the end, the uncommitted changes will
> hang around, and the next request may end up committing those changes
> unwittingly. I rollback at both the beginning and the end of all requests,
> just to be safe :)
Ok, thanks everybody, I've got. You do rollback just to be sure that you
don't leave things open... That's an interesting technique.
On Thu, 14 Dec 2000, Bruce W. Hoylman wrote:
> Interesting behavior ... I did not know Apache::DBI did this!
Changes:
0.84 August 21, 1999
- the PerlCleanupHandler in Apache::DBI.pm, which is supposed
to initiate a rollback in case AutoCommit is off, will only be
created, if the initial data_source sets AutoCommit to 0.
0.82 June 03, 1999
- proposal from Honza Pazdziora <adelton@informatics.muni.cz>:
add PerlCleanupHandler in Apache::DBI, which issues a rollback
unless AutoCommit is on.
===