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

[M.-A. Lemburg]

Ian Bicking wrote:
> James Y Knight wrote:
>> I'm worried about database access. Most DBAPI adapters have  
>> threadsafety level 2: "Threads may share the module and  
>> connections.". So with those, at least, it should be fine to move a  
>> connection between threads, since "share OK" implies "move OK".  

What exactly do you mean with "move" ? Sharing a
connection refers to multiple threads creating cursors
on this connection.

>> However, no documentation I've found has said anything separately  
>> about whether it's safe to _move_ a cursor between threads. It seems  
>> likely to me that it would not be safe, at least in some database  
>> adapters.

Thread level 3 adapters would allow for sharing a cursor
meaning that you can call cursor.execute() from different
threads.

Given that you usually already have to be careful with
sharing connections, sharing cursors is rather unlikely
to work in a general setting.

> And if it's not safe, that means a WSGI result iterator  
>> cannot use any DBAPI cursor functionality which seems a drag.
>>
>> Does anybody have practical experience with the safety of moving a  
>> DBAPI cursor between threads?
> 
> I haven't done that, but SQLite (2?) notably doesn't allow you to move a 
> connection between threads.  I'm not actually sure what problems it 
> causes if you do move them -- it may simply be an overzealous warning.
> 
> CCing DB-SIG -- people there might know more details.

Sharing cursors is possible with some database drivers and
can be used to e.g. pool cursors with prepared commands.

mxODBC does support this if the ODBC driver is thread-safe
(which it should be if it adheres to the ODBC standard).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 19 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 ! ::::