[Web-SIG] [DB-SIG] WSGI thread affinity/interleaving

[M.-A. Lemburg]

Guido van Rossum wrote:
> On 12/19/05, M.-A. Lemburg <mal at egenix.com> wrote:
>> Ok. In that sense, I think "moving" is not really possible
>> with database connections or cursors: these always rely on
>> external resources and these may be relying on having the
>> same thread context around when being called.
>>
>> Why would you want to "move" cursors or connections around ?
> 
> A typical connection (or cursor) caching implementation used from a
> multi-threaded program might easily do this: a resource is created in
> one thread, used for a while, then given back to the cache; when
> another thread requests a resource, it gets one from the cache that
> might have been used previously in a different thread. Keeping a cache
> per thread is a bit cumbersome and reduces the efficacy of the cache
> (if a thread goes away all the resources cached on its behalf must be
> closed instead of being made available to other threads).
>
> I'm not sure I understand what resources a typical DB client library
> might have that are associated with a thread instead of with a
> connection or cursor -- IOW I don't understand why you think moving
> resources between threads would be a problem, as long as only one
> thread "owns" them at any time. IOW if I maintain my own locking, why
> would I still be limited in sharing connections/cursors between
> threads? What am I missing?

All this would be easily possible if the Python cursor object
had full control over the external resources in use.

However, most Python database cursor objects rely on external
libraries and therefore do not have control over where state
is stored.

If you use a resource from a different thread than the one
where it was created, this can cause situations where part
of the state is missing, or worse, a different state is
used.

Many database libraries do their own caching at various
levels (network connection, logical connections, cursors).
Not all of them are fully thread-safe and its hard to find
out.

To be on the safe side, you should only use connections from
the thread they were created with.

It's still worthwhile to cache the connections (and even
cursors on that connection):

* connecting to a database can take anything from micro-seconds
to several seconds;

* preparing statements for execution on a cursor also takes
  time (and in most cases costs a network roundtrip), so
  caching already prepared cursors also makes sense for
  commonly used statements.

The latter is especially useful with bound parameters since
the database will usually only have to prepare the statement
once and can then take any number of parameter sets to
execute the statement with.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 31 2005)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::