Umm, here's another thought... How about changing prepare_cached so it includes the caller() info in the hash key by default. That way prepare_cached won't return the same statement handle if called from two different places even if the $statement is the same.
Tim. On Wed, Feb 27, 2002 at 10:38:16PM -0600, Stephen Clouse wrote: > Well, we finally discovered how prepare_cached can "cause problems", as the docs > so eloquently state. I'm amazed we evaded it this long. We use prepare_cached > literally everywhere for performance reasons, so it was only a matter of time > before somewhere, 10 or 15 levels deep in method calls, someone tried to reuse a > statement while it was active somewhere farther up the call stack. > > I had solved this problem in our DBI subclass, but this seems a useful (and > benign) enough change that I went ahead and integrated it into our local DBI > sources. Tiny code and large doc patch attached. Do with it what you will. > > -- > Stephen Clouse <[EMAIL PROTECTED]> > Senior Programmer, IQ Coordinator Project Lead > The IQ Group, Inc. <http://www.theiqgroup.com/> Content-Description: dbi-prepare_cached_hack.patch > Index: DBI.pm > =================================================================== > RCS file: /home/cvs/patched-sources/DBI/DBI.pm,v > retrieving revision 1.1.1.1 > diff -u -r1.1.1.1 DBI.pm > --- DBI.pm 28 Feb 2002 01:05:12 -0000 1.1.1.1 > +++ DBI.pm 28 Feb 2002 04:31:46 -0000 > @@ -1239,7 +1239,8 @@ > my $key = ($attr) ? join("~~", $statement, @attr_keys, @{$attr}{@attr_keys}) : >$statement; > my $sth = $cache->{$key}; > if ($sth) { > - if ($sth->FETCH('Active')) { > + $allow_active = 0 if !defined $allow_active; > + if ($sth->FETCH('Active') && $allow_active != 2) { > Carp::carp("prepare_cached($statement) statement handle $sth was still >active") > if !$allow_active; > $sth->finish; > @@ -2879,14 +2880,6 @@ > corresponding cached C<$sth> will be returned without contacting the > database server. > > -This caching can be useful in some applications, but it can also cause > -problems and should be used with care. If the cached C<$sth> being > -returned is active (i.e., is a C<SELECT> that may still have data to be > -fetched) then a warning will be generated and C<finish> will be called > -for you. The warning can be suppressed by setting C<$allow_active> to > -true. The cache can be accessed (and cleared) via the L</CachedKids> > -attribute. > - > Here are some examples of C<prepare_cached>: > > sub insert_hash { > @@ -2908,6 +2901,44 @@ > $sth = $dbh->prepare_cached("SELECT * FROM $table $qualifier"); > return $dbh->selectall_arrayref($sth, {}, @values); > } > + > +Caveat emptor: This caching can be useful in some applications, but it can also > +cause problems and should be used with care. Here is a contrived case where > +caching would cause a significant problem: > + > + my $sth = $dbh->prepare_cached('SELECT * FROM foo'); > + $sth->execute(@values); > + while (my $data = $sth->fetch) { > + my $sth2 = $dbh->prepare_cached('SELECT * FROM foo'); > + $sth2->execute(@more_values); > + while (my $data2 = $sth2->fetch) { > + do_stuff($data,$data2); > + } > + } > + > +In this example, since both handles are preparing the exact same statement, > +C<$sth2> will not be its own statement handle, but a duplicate of C<$sth> > +returned from the cache. The results will certainly not be what you expect, > +and, depending on the pickiness of the SQL engine, may throw an error when it > +returns to the outer loop expecting to fetch more data when the inner loop has > +already exhausted it. The C<$allow_active> parameter lets you adjust DBI's > +behavior when prepare_cached is returning a statement handle it sees as active. > +There are three settings: > + > +=over 4 > + > +0: A warning will be generated, and C<finish> will be called on the statement > +handle before it is returned. This is the default behavior if C<$allow_active> > +is not passed. > + > +1: C<finish> will be called on the statement handle, but the warning is > +suppressed. > + > +2: DBI will not touch the statement handle before returning it. You will need > +to check C<$sth->E<gt>C<{Active}> on the returned statement handle and deal > +with it in your own code. > + > +=back > > > =item C<commit>
