[pysqlite] Connection deadlock because of file lock

Hi all.  I recently was experimenting with sqlite concurrency.  The 
commonly repeated meme is "don't use sqlite when you need concurrency"- 
the problem is I couldn't find any actual metrics anywhere, so I started 
benchmarking my own.  That is, I wanted to know at what point 
concurrency starts being a problem (5 simultaneous?  100 simultaneous? 
at what point do you get a 10% drop in performance versus serial access? 
etc.)

Initially, the core of my benchmarking suite was something like this:


#---------------------------
import sqlite3, time
def do_insert(conn):
    conn.execute("""
        insert into stocks values('2009-01-05', 'Test', 'Test', 100, 10.13)
        """)
    conn.commit()

conn = sqlite3.connect('for_testing.sqlite3')
for i in range(0,100):
    do_insert(conn)
#---------------------------


If I spawned many of these at the same time, one always finished and all 
the others always exit with (as expected) an OperationalError: database 
is locked.
The only surprise for me there was that only one finished- surely one of 
the others would, after some timeout, be able to acquire the lock and do 
its work as well?  So I increased the timeout on the sqlite3.connect() 
function.  No matter what I set it to, only one process succeeds, and 
the others wait for however long before failing.

OK, so at this point I'm surprised, but I figure I'll just do a quick 
workaround, since, again, I'm trying to measure sqlite concurrency:


#---------------------------
import sqlite3, time
def do_insert(conn):
    try:
        conn.execute("""
            insert into stocks values('2009-01-05', 'Test', 'Test', 100, 
10.13)
            """)
        conn.commit()
    except sqlite3.OperationalError:
        time.sleep(0.1)
        do_insert(conn)                       #  <-----   Wait for a bit 
and try again

conn = sqlite3.connect('for_testing.sqlite3')
for i in range(0,100):
    do_insert(conn)
#---------------------------

So here if it sees that the db is locked it simply waits for a bit and 
tries again (recursively- not pretty, but that's beside the point).

Again, one of these spawned processes is able to do it's things, but the 
rest continue to get OperationalErrors forever after- even after that 
first one is done and has released its lock.

Finally, the only thing that worked was for me to completely 
re-establish the connection each time, ala:

#---------------------------
def do_insert():
    try:
        conn = sqlite3.connect('example.db', timeout=0.1)
        conn.execute("""
                insert into stocks
                values('2006-01-05','BUY','RHAT',100,35.14)""")
        conn.commit()
        conn.close()
    except sqlite3.OperationalError:
        conn.close()
        time.sleep(0.1)
        do_insert()

for i in range(0,10):
    do_insert()
#---------------------------

Now, finally, processes keep trying to do their work until they succeed, 
and I was able to do my benchmarking.

I get the sinking feeling that a lot of the problem people have with 
sqlite's concurrency is not that it's large-grain locks make it that 
much less concurrent, but that it has a lot more to do with people's 
experience with how libraries like pysqlite handle and propagate the db 
lock message.  With my third solution above, I found that sqlite easily 
scales to hundreds of simultaneous users (_really_ simultaneous- as in 
all trying to write to the DB at the exact same time) in line with mysql 
and others- at 500+ simultaneous users you start to get about a 10% drop 
because of lock overhead.  Not spectacular, but not shabby either.

The problem is that anecdotally people using, say, django, encounter the 
database-is-locked error even at very, very minimal loads, because 
pysqlite requires the connection to be completely reset when it 
encounters a lock- even if the timeout parameter is set.  That gives 
people a sense that sqlite is not nearly as powerful as it actually is 
(which still may not match, say, Berkeley db or something, but still!)

I've gone through the django code and verified for myself that it will 
just blindly propagate the db-locked problem without trying to reset the 
connection.  It is, in fact, a bit unintuitive.

Is this something that can be addressed?  Am I missing something?  I 
_really_ appreciate the work that has gone into this library.  I feel 
that opening up this concurrency bottleneck will spread sqlite's 
adoption and this library's utility significantly.  We can stop hearing 
people say "oh, yeah, that's just sqlite for you- upgrade to mysql..." 
until much higher loads...

-Joseph