Re: pysqlite design decisions

Gerhard Häring wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Christian Boos wrote:
>   
>> glyph-TyWPi3/[email protected] wrote:
>>     
>>> ...
>>>       
>>>> | I've heard, sqlite itself is only middling good at dealing with multiple
>>>> | threads.
>>>>     ...
>>> The impression may simply have been garnered from the proximity of threading bugs to database bugs in extremely poorly tested projects such as Trac.
>>>   
>>>       
>> Pleased to hear that ;)
>> Agreed, in Trac we had a bunch of issues with multi-threading, locks, 
>> etc. but in the process we also helped to improve the bindings in this 
>> area. And I think that with SQLite (>= 3.3.4) and recent pysqlite, the 
>> infamous "database is locked" issue doesn't happen anymore in 0.10.2 and 
>> 0.11dev, thanks to the ability to pool the connections and reuse them 
>> from one request/thread to the next.
>>     
>
> I hate that issue. Really hate it. Really REALLY hate it. Because the
> question wether you can share pysqlite connections among threads comes up
> every so often, and I cannot give an honest answer.
>
> I searched hard and read http://sqlite.org/faq.html#q8 again, and again.
>
> According to that, sharing is only *really* safe if all statements are
> finalized, and that's certainly not the case with pysqlite connections,
> because you can be pretty damn sure that the connection contains prepared
> statements that are in *reset* state, but not *finalized*. 

I see, I think I missed the distinction between reset and finalized.
Would it be possible to have a way to clear the statement cache (= 
finalize the statements), in order to see if this would help in the few 
problematic situations left (e.g. http://trac.edgewall.org/ticket/3790, 
http://trac.edgewall.org/ticket/4331).
I've also noticed that there's a minimal size for the statement cache, 
so it's not possible to disable it completely for testing. That would be 
useful as well.

> It may even be
> possible that a transaction is still open.
>   

We do a rollback before putting the connection back into the pool.

> That's why the check_same_thread is still True by default. I even went as
> far as documenting that flag not at all in the sqlite3 module so
>
> a) I don't have to answer that annoying questions :-P
> b) I can remove the check when I'm certain SQLite is really threadsafe
> enough one day without it
>   

I understand. However, introducing the reuse of connections between 
threads was really the end of the database locks that were so frequent 
with Trac/pysqlite. The reason why is not 100% clear: it's probably 
because that way we avoid some steps that are prone to locks. It could 
be either because we avoid the creation of new connection steps or the 
preparation of new statements, thanks to the statement cache which 
remains alive, or both (see  http://trac.edgewall.org/ticket/3446 for 
the whole story).

-- Christian