[Web-SIG] WSGI thread affinity/interleaving

[Gerhard Häring]

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".  
>> 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. 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.

It's the same for SQLite 3. The problem is, as far as I understand, that 
POSIX file locks don't work reliably when they're accessed from multiple 
threads. That's why the SQLite *docs* always said that you cannot share 
a SQLite database handle between threads.

And pysqlite as well as apsw both fire exceptions if you try to do so. 
In recent SQLite 3.x versions, SQLite itself would detect this and 
return an error on *nix too FWIW.

pysqlite does have an option to turn the check off, for people who want 
to shoot themselves in the foot. Fortunately for them, they nowadays get 
an error-message from SQLite on non-Windows systems anyway ;-)

-- Gerhard