connect_cached and transactions

Hi

I've written a CGI script (not mod_perl) and am looking to use
connect_cached to improve connection times. However, I have a couple of
questions.

Does it work in a non-persistent environment? I assume so, since there's no
distinction made in the DBI docs, but I'd like to be sure.

Are there any gotchas with transactions? The DBI connect_cached code
contains the following lines:

if ($dbh && $dbh->FETCH('Active') && eval { $dbh->ping }) {
# XXX warn if BegunWork? <-- this has got me wondering
# XXX warn if $dbh->FETCH('AutoCommit') != $attr->{AutoCommit} ?
# but that's just one (bad) case of a more general issue.
return $dbh;
}

Does this imply that if my code needs to be transaction safe then I
shouldn't use connect_cached? Could two user sessions collide with each
other if one needs to roll back while the other is performing a DML
statement?

Regards

Dan Horne

Re: connect_cached and transactions

On Tue, Nov 01, 2005 at 08:57:49AM +1300, Dan Horne wrote:
> Hi
>
> I've written a CGI script (not mod_perl) and am looking to use
> connect_cached to improve connection times. However, I have a couple of
> questions.
>
> Does it work in a non-persistent environment? I assume so, since there's no
> distinction made in the DBI docs, but I'd like to be sure.
>
> Are there any gotchas with transactions? The DBI connect_cached code
> contains the following lines:
>
> if ($dbh && $dbh->FETCH('Active') && eval { $dbh->ping }) {
> # XXX warn if BegunWork? <-- this has got me wondering
> # XXX warn if $dbh->FETCH('AutoCommit') != $attr->{AutoCommit} ?
> # but that's just one (bad) case of a more general issue.
> return $dbh;
> }
>
> Does this imply that if my code needs to be transaction safe then I
> shouldn't use connect_cached? Could two user sessions collide with each
> other if one needs to roll back while the other is performing a DML
> statement?

connect_cached will return a 'matching' $dbh even if that $dbh is in
the middle of a transaction. Apache::DBI takes care to install handlers
that ensure any transaction is committed/rolledback before the next
request is handled. If you're not using Apache::DBI you'll need to do
the same.

Tim.

RE: connect_cached and transactions

OK, understood

Thanks

Dan

> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Tuesday, 1 November 2005 9:51 a.m.
> To: Dan Horne
> Cc: dbi-users@perl.org
> Subject: Re: connect_cached and transactions
>
> On Tue, Nov 01, 2005 at 08:57:49AM +1300, Dan Horne wrote:
> > Hi
> >
> > I've written a CGI script (not mod_perl) and am looking to use
> > connect_cached to improve connection times. However, I have a couple of
> > questions.
> >
> > Does it work in a non-persistent environment? I assume so, since there's
> no
> > distinction made in the DBI docs, but I'd like to be sure.
> >
> > Are there any gotchas with transactions? The DBI connect_cached code
> > contains the following lines:
> >
> > if ($dbh && $dbh->FETCH('Active') && eval { $dbh->ping }) {
> > # XXX warn if BegunWork? <-- this has got me wondering
> > # XXX warn if $dbh->FETCH('AutoCommit') != $attr->{AutoCommit} ?
> > # but that's just one (bad) case of a more general issue.
> > return $dbh;
> > }
> >
> > Does this imply that if my code needs to be transaction safe then I
> > shouldn't use connect_cached? Could two user sessions collide with each
> > other if one needs to roll back while the other is performing a DML
> > statement?
>
> connect_cached will return a 'matching' $dbh even if that $dbh is in
> the middle of a transaction. Apache::DBI takes care to install handlers
> that ensure any transaction is committed/rolledback before the next
> request is handled. If you're not using Apache::DBI you'll need to do
> the same.
>
> Tim.