Re: Connection pool issues with clsql

On 2/2/2010 4:54 PM, Carlos Konstanski wrote:
> On Tue, 2 Feb 2010, Mackram Raydan wrote:
>
>> Date: Tue, 2 Feb 2010 23:40:23 +0200
>> From: Mackram Raydan <[email protected]>
>> To: clsql <[email protected]>
>> Subject: [CLSQL] Connection pool issues with clsql
>>
>> Hey everyone,
>>
>> I am attempting to use the clsql in a production enviroment that is
>> running
>> Ubuntu 9.04 server edition (64 bit). In order to have good
>> performance I am
>> attempting to use a pool of connections however I am faced with the
>> problem
>> that every time I attempt to do a connection disconnect to return the
>> thread
>> to the pool I get an error and the whole thing crashes. Without
>> pooling the
>> system runs fine. Any one faced such a thing before?
>>
>> Any help is much appreciated.
>>
>> Regards,
>>
>> Mackram Raydan
>>
>> Company: www.imagimate.com
>> Website: www.trailoflight.net
>>
>> "An invasion of armies can be resisted, but not an idea whose time has
>> come." Victor Hugo
>
> I may have run into this a long time ago, if memory serves. It was too
> long ago to be relevant today (I hope). Anyway, I abandoned connection
> pooling, and adopted the methodology of opening a connection at the
> beginning of a request, using that connection for the lifetime of the
> request, and closing it at the end. Things run fast if you avoid
> opening and closing connections during a transaction (I use the word
> "transaction" loosely here to mean "all the stuff that happens during
> one user operation, or request").
>
> Carlos
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql

From what I recall looking at, connection pooling still has some
potential for race conditions from here:
http://lists.b9.com/pipermail/clsql/2009-June/001717.html.

Also if you are using view-class objects, each object stores its
connection and uses that one, so if you return a connection to the pool
it can still be referenced and used by an object even as another object
is trying to use that same connection in a different thread. I.e. don't
let objects outlive a connection to the database.

Also there are a couple of patches here:
http://github.com/UnwashedMeme/clsql/commits/uncontroversial/sql/pool.lisp
that have not yet been merged into master (they're on the list) attempt
to verify that a connection gotten from the database is still in working
order--normally another query to the database to ensure validity. This
still tends to be faster than building a new connection altogether.


-- 
Nathan Bird
[email protected]
http://www.acceleration.net/ 
Custom Programming, Design, Hosting, and Broadband.