<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Dan Horne wrote:<br>
<blockquote cite="mid20060430041029.62A1B127FA@mail.loomsday.co.nz"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
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.<br>
<br>
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:<br>
<blockquote><i>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.<br>
<br>
</i></blockquote>
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).<br>
<br>
HTH<br>
<br>
Tim<br>
<br>
</body>
</html>