[Zope-dev] Making a ZSQL.DA fully multi-threaded?

[Ganesha Shanmuganathan]

Hi,

On Fri, 2004-07-16 at 09:36, Brad Clements wrote:
> On 16 Jul 2004 at 8:57, Chris Withers wrote:
> 
> > This often means that many more database connections are opened than are
> > actually necessary. And if you start timing out inactive database
> > connectiosn on the database side, you're in for a world of pain and
> > suffering.
> > 
> > I branched ZOracleDA some time ago to move away from this model for that
> > very reason...
> > 
> > What's the actual problem you're experiencing?
> 
> I have set the SAPDB timeout to 32400 seconds. However when running with more than 1 
> thread I still get "mysterious segfaults" in the sapdbapi component.
> 
> The SAP  folks say their adapter is "multi-thread capable", but I've come to believe that only 
> means that it's ok to open a connection in one thread and use it in another, but NOT to have 
> 2 threads make requests on the same connection at the same time.
Yes. It is defined in DBAPI and breaking it is not a good idea as it
will cause problems like when one thread writes data, and another thread
on the same connection wants to read it before the first can commit. 
> 
> Also, two threads can' t open a connection "at the same time" because their connection table 
> management isn't "thread safe".
> 
> What I need, I suppose, is a DB connection pool mechanism of some kind. I also have 
> Python extensions that need a connection as well. Getting the actual database connection 
> from a DA stinks (ie, it's not really possible). I'd like to see an API extension to ZopeDA's to 
> allow Pythonscripts, Products and external methods get an actual db connection. But that I 
> suppose is a problem to solve later.

You have to change ZopeDA to do that. But I doubt if it will be
beneficial. We were experimenting with Pscopg and its Zope adapter.
Psycopg has an option of opening many physical connections (one physical
connection per cursor) to the DB using a non-serialize mode(yes that
part breaks compatibility from DBAPI and suffers from the problem
mentioned above)

When we bench marked using AB, it was found that non serialized mode is
very slow even though the threads were accessing the database in
parallel because of the overhead of setting up new connections. To
implement connection pooling, I think it is best to do it using an
externel product like SQLRelay than at the driver level.

thanks,
ganesh

Zeomega LLC
Open minds' Open Solutions

Plano, Texas, USA
Bangalore, India
phone: 972-731-6750
http://www.zeomega.com


> 
> I think I will take a look at ZOracleDA to see what you did. 
>