Re: Using Apache::DBI

[Jay Buffington <ja...@gmail.com>]

Reviving a thread 4 months later...

On 5/10/06, Perrin Harkins <pe...@elem.com> wrote:
> On Wed, 2006-05-10 at 10:26 -0700, Jay Buffington wrote:
> > At first I was caching the database handle myself when running outside
> > of mod_perl.  In mod_perl I was relying on Apache::DBI returning the
> > same handle every time I called connect().  That didn't work.
>
> What didn't work about it?  If you use the same parameters, you should
> always get the same handle.

Ohhh, I misunderstood. It's not a pool of pools.  "Practical mod_perl"
refers to a 'pool' of connections
(http://modperlbook.org/html/ch20_01.html#pmodperl-CHP-20-SECT-1).  I
took this to mean that it maintains several connections for each
connection string, but after reading through the Apache::DBI source I
see that the pool he is referring to is the different connection
strings.

But, that handle might have expired, so it is possible to get a
different handle.  I'm worried that it's not safe to do this:

use Apache::DBI;
use DBI;

my $dbh = DBI->connect(...);
$dbh->do("insert values('foo') into bar');

# time passes... and then:

my $new_dbh = DBI->connect(...);
$new_dbh->commit();

Since transactions are handle specific and there is no guarantee that
I'm going to get the exact same handle, I'm scared my insert might not
get committed.

Of course, I might just be whining about a corner case that never
exists in real life.  If the handle is active when I do the insert,
it's probably going to be active when I do the commit moments later.

> > I considered allocating a handle in the pnotes() at the beginning of
> > every request using a PerlInitHandler, but that is expensive,
> > especially since many requests don't even use a handle.
>
> Are the requests that don't need a database running mod_perl code?  If
> not, they should be served by a proxy.

Good point.  Static requests are served from a squid cache a light
weight web server (Perlbal).

Thanks,
Jay

Re: Using Apache::DBI

[Jonathan Vanasco <mo...@2xlp.com>]

On Sep 7, 2006, at 4:40 PM, Jay Buffington wrote:

> This solves a problem I was having some time ago while implementing
> ecommerce (verisign):
>
> 1. insert customer's info
> 2. authorize credit card
> 3.  log authorization response to database
> 4. if auth fails roll back #1, but keep #3

Nested transactions for problems like that require DB supported  
nested transactions-- oracle and postgres have them ( i think ).

DBIx is just transaction aware- it lets you nest your transactions by  
hijacking begin/commit|rollback.  Errors on an inner transaction  
create errors for the whole nested structure.
IE: You can't rollback to 1 from 3- you need to do that in the database.

It's useful for situations where you're  wrapping a bunch of modules  
or 'pages' into a larger chain of commands.

I usually avoid your problem by using different db handles per-child  
-- one of which does not support transactions , is dedicated for all  
logging issues, and only has write on a few tables.

Re: Using Apache::DBI

[Jay Buffington <ja...@gmail.com>]

This solves a problem I was having some time ago while implementing
ecommerce (verisign):

1. insert customer's info
2. authorize credit card
3.  log authorization response to database
4. if auth fails roll back #1, but keep #3

I was planning on solving this problem using Oracle's
AUTONOMOUS_TRANSACTION Pragma.  I'll look into nested transactions and
see how these two differ.

Thanks,
Jay

On 9/7/06, Jonathan Vanasco <mo...@2xlp.com> wrote:
>
> take a look at:
>
>         http://search.cpan.org/~crakrjack/DBIx-Transaction-0.008/
>
> which allows for nested transactions in a DBI handle.

Re: Using Apache::DBI

[Jonathan Vanasco <mo...@2xlp.com>]

take a look at:

	http://search.cpan.org/~crakrjack/DBIx-Transaction-0.008/

which allows for nested transactions in a DBI handle.

I use something homegrown and similar (but not stable enough for  
cpan), that intercepts calls to connect/disconnect and treats them as  
begin/end commands on a nested transaction

note that either solution aren't true db style nested transactions  
( i think only pg and oracle have them anyways ).  they just  
intercept begin/end if you're already in a transaction.

Re: Using Apache::DBI

[Perrin Harkins <pe...@elem.com>]

On Thu, 2006-09-07 at 12:29 -0700, Jay Buffington wrote:
> But, that handle might have expired, so it is possible to get a
> different handle.

It's not likely, but it is possible.  You'd need to have some transient
error that caused the connection to die between DBI calls.  (Not during
a call, since you have RaiseError on, right?)

>   I'm worried that it's not safe to do this:
> 
> use Apache::DBI;
> use DBI;
> 
> my $dbh = DBI->connect(...);
> $dbh->do("insert values('foo') into bar');
> 
> # time passes... and then:
> 
> my $new_dbh = DBI->connect(...);
> $new_dbh->commit();

My approach is to keep AutoCommit on, and turn it off for the brief
periods where I want a transaction to span multiple statements.

{

    local $dbh->{AutoCommit} = 0;
    ### do some SQL actions here

} # commit happens automatically here

> Of course, I might just be whining about a corner case that never
> exists in real life.  If the handle is active when I do the insert,
> it's probably going to be active when I do the commit moments later.

If it worries you, you could modify Apache::DBI to die rather than
reconnect if the handle has been requested previously in the current
request.  This is pretty easy to keep track of with pnotes.

- Perrin