[Dbix-class] Caching connections

[Tim Sweetman]

Dan Horne wrote:

>The thing is, I need multiple schemas for transactional reasons - I don't
>want one person to inadvertently commit or rollback another person's
>transactions.  The way my (non-DBIC) CGI::Application-based app works
>currently:
>
>Front end users are non transactional - they just view a site.
>Backend (i.e. admin) users require transactions. They each get their own
>connection, based on their username:
>
>$self->{_dbh} = DBI->connect_cached(
>            $self->conf->param('dsn'), $self->conf->param('user'),
>            $self->conf->param('password'), {username => $username,}
>        );
>
>I'd like to do something similar if I switch to DBIC, but the only way I can
>see it working is to create a schema for each user, passing the username as
>a DBI attribute (as above) to the Schema's connect method. But I want the
>connection to persist. 
>
>As far as I can tell from the DBIC code, allowances are made for Apache::DBI
>under mod_perl, but I'd like similar consideration for FastCGI and
>PersistentPerl and I think my problem is solved via DBI's connect_cached.
>  
>
I presume that this is using session affinity to tie the administrative 
users to a particular process, so they pick up the same DB handle again.

I suspect a lot of logic (in Apache::DBI and elsewhere) implicitly 
treats database handles as basically stateless. This is always going a 
little bit out on a limb, since ALTER SESSION statements, LOCK TABLEs, 
and indeed transactions, are stateful. Example: the DBI documentation:

    /Caching connections can be useful in some applications, but it can
    also cause problems, such as too many connections, and so should be
    used with care. In particular, avoid changing the attributes of a
    database handle created via connect_cached() because it will affect
    other code that may be using the same handle.

    /

I believe the thinking behind DBI's design is that DBH caching is an 
optimisation for performance, and intended to have no effect on the 
system's logic. Conversely, if you're using session affinity & making 
database transactions span HTTP requests. (This is, as I understand it, 
impossible to do reliably using mod_perl, because the assignation of 
HTTP requests to Perl processes is unpredictable).

HTH

Tim

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060430/a0c7758c/attachment.htm