atom feed23 messages in org.perl.dbi-devRe: AutoCommit and connect_cached()
FromSent OnAttachments
David WheelerJan 31, 2005 10:28 pm 
Tim BunceFeb 1, 2005 2:44 am 
David WheelerFeb 1, 2005 9:16 am 
Tim BunceFeb 2, 2005 2:59 am 
David WheelerFeb 2, 2005 10:50 am 
David WheelerFeb 2, 2005 10:58 am 
Tim BunceFeb 3, 2005 7:45 am 
David WheelerFeb 3, 2005 8:40 am 
Tim BunceFeb 3, 2005 3:30 pm 
David WheelerFeb 3, 2005 3:49 pm 
Tim BunceFeb 4, 2005 2:08 am 
David WheelerFeb 4, 2005 10:38 am 
Sheikin SergeiFeb 4, 2005 11:31 am 
Michael A ChaseFeb 4, 2005 12:22 pm 
David WheelerFeb 11, 2005 11:18 am 
Tim BunceFeb 12, 2005 10:11 am 
David WheelerFeb 12, 2005 4:35 pm 
Tim BunceFeb 13, 2005 2:44 pm 
David WheelerFeb 13, 2005 5:54 pm 
Tim BunceFeb 14, 2005 3:09 am 
David WheelerFeb 15, 2005 10:38 am 
Tim BunceFeb 15, 2005 2:22 pm 
David WheelerFeb 15, 2005 9:01 pm 
Subject:Re: AutoCommit and connect_cached()
From:David Wheeler (
Date:Feb 2, 2005 10:50:20 am

On Feb 2, 2005, at 2:59 AM, Tim Bunce wrote:

Try DBI->connect_cached which calls DBI->connect which does:

unless ($dbh = $drh->$connect_meth($dsn, $user, $pass, $attr)) { ... } ... if (%$attr) { ... my $a; foreach $a (qw(RaiseError PrintError AutoCommit)) { # do these first next unless exists $attr->{$a}; $dbh->{$a} = delete $attr->{$a}; } foreach $a (keys %$attr) { eval { $dbh->{$a} = $attr->{$a} } or $@ && warn $@; } }

Ah, I see. But loking at DBI::_::dr::connect_cached, it looks as though connect() isn't called if it finds an active, pingable driver in the cache. I must still be missing something...

Note that drivers have the opportunity to apply %$attr themselves during their own connect call and deleted those applied attributes from %$attr so the DBI won't have any left to apply.

Uh, "their own connect call"? Can you tell me more about what this means? I'd love to be able to remove attributes before the call to connect() if the driver already exists. Would I have to override connect_cached() in a subclass, perhaps?

Certainly could be done. The DBI's default DBI::_::dr::connect_cached method would just need to do %$attr = () if ...;

Yes, quite. I just tested this and it worked great for me (Though it still always returns a new handle for DBD::SQLite for some reason...):

Index: =================================================================== --- (revision 856) +++ (working copy) @@ -1403,6 +1403,7 @@ my $cache = $drh->FETCH('CachedKids'); $drh->STORE('CachedKids', $cache = {}) unless $cache;

+ my $no_reset = delete $attr->{NoReset}; my @attr_keys = $attr ? sort keys %$attr : (); my $key = do { local $^W; # silence undef warnings join "~~", $dsn, $user||'', $auth||'', $attr ? (@attr_keys,@{$attr}{@attr_keys}) : () @@ -1412,6 +1413,7 @@ # XXX warn if BegunWork? # XXX warn if $dbh->FETCH('AutoCommit') != $attr->{AutoCommit} ? # but that's just one (bad) case of a more general issue. + %$attr = () if $no_reset; return $dbh; } $dbh = $drh->connect(@_);

If this looks good to you, I can add a test for it and update the docs and send that patch.

Here's what I've added to the docs for now:


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.

Where multiple separate parts of a program are using connect_cached() to connect to the same database with the same (initial) attributes it is a good idea to add a private attribute to the connect_cached() call to effectively limit the scope of the caching. For example:

DBI->connect_cached(..., { private_foo_cachekey => "Bar", ... });

Handles returned from that connect_cached() call will only be returned by other connect_cached() call elsewhere in the code if those other calls also pass in the same attribute values, including the private one. (I've used C<private_foo_cachekey> here as an example, you can use any attribute name with a C<private_> prefix.)

Taking that one step further, you can limit a particular connect_cached() call to return handles unique to that one place in the code by setting the private attribute to a unique value for that place:

DBI->connect_cached(..., { private_foo_cachekey => __FILE__.__LINE__, ... });

By using a private attribute you still get connection caching for the individual calls to connect_cached() but, by making separate database conections for separate parts of the code, the database handles are isolated from any attribute changes made to other handles.


Good information, thanks.

At this point I'm undecided about supporting something like a NoReset attribute - but mainly because it would probably become the default behaviour if I make other changes to handle creation that I'm thinking of.

Oh? Such as...?

Also, it may be possible to get the same effect by adding more general callback hooks:

$dbh = DBI->connect_cached(..., { OnConnect => sub { print "New connection" }; OnConnectCached => sub { print "Old connection"; my (...,$attr)=@_; %$attr = () }; });

Ooh, is that documented? I only see OnConnect at line 655 (in svn). But yeah, if there was an OnConnectCached (which, I assume, would only be called if it found a handle in the cache), then it would indeed do the trick.

Still, it's too late for 1.47 now.

Just let me know how you'd like to proceed with these issues in 1.48 and I'll do what i can to help (such as with the NoReset patch).