Re: [ANN] pysqlite 3.0 alpha1

Hello,

Gerhard Häring wrote:
> During the last two months I wrote a new implementation of pysqlite that 
> should be as compatible to the existing one as possible, but is based on 
> ctypes. The goal is to (hopefully) provide PyPy with an implementation 
> of the sqlite3 module in its standard library.
>
> It would be great if you could test how this release works for you. The 
> API *should* be compatible to pysqlite 2.x in theory.
>   

I had a test run with Trac this morning, and things look promising. 
First, everything works, the performance seems to be the same as with 
the C extension (sqlite3 or pyqslite 2.4.1), so that's already a great 
achievement!

However, concurrent requests were troublesome, as always. Multiple 
concurrent reads are fine, but as soon as there's a writer, then the 
assert in dbapi2.py at line 696 fires, as the sqlite3_step call returns 
SQLITE_BUSY. Multiple concurrent write attempts exhibit the same issue, 
plus "OperationalError: SQL logic error or missing database" in commit 
or rollback, and sometimes even "OperationalError: database is locked".

Those tests were performed on Windows, using the latest official build 
of SQLite (3.5.6), an unmodified pysqlite 3.0a1, latest Trac trunk (btw, 
in Trac, disabling poolable connections didn't make a difference).

Now, before sending the above report, as this reminded me so much of 
issue #126, I had a try of the solution you implemented for that ticket 
back then, i.e. using sqlite3_busy_timeout. To my great surprise, that 
was apparently enough to make things work without a hitch. I was not 
able to trigger the assert again, not even a single small "database is 
locked" error ;-)
Patch attached.

> ...
> You will need a recent SQLite release for this (I believe 3.5 or 
> higher). 

Right, the one shipped with Python 2.5 (i.e. SQLite 3.3.4) lacks the 
sqlite3_prepare_v2 function. If one really wants to make it work with 
3.3.4, replacing sqlite3_prepare_v2 by sqlite3_prepare in dbapi2.py 
seems to work, but as there are semantic differences between the two 
functions, I'm sure it's not really advised to do so.

-- Christian

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite

# HG changeset patch
# User Christian Boos <[email protected]>
# Date 1204194729 -3600
# Node ID 547c5b1a87e27009ab2ef7bc4a7d450d5666ca37
# Parent  9327e187002fb3e92d20d87b944fe2ef38ec7f6e
Enable `sqlite3_busy_timeout` on the sqlite3 connection when the `timeout` parameter is given to the `Connection` constructor.

This is apparently enough to make concurrent requests (multiple readers and writers) work smoothly.

diff --git a/pysqlite2/dbapi2.py b/pysqlite2/dbapi2.py
--- a/pysqlite2/dbapi2.py
+++ b/pysqlite2/dbapi2.py
@@ -241,10 +241,13 @@
     return factory(database, **kwargs)
 
 class Connection(object):
-    def __init__(self, database, isolation_level="", detect_types=0, *args, **kwargs):
+    def __init__(self, database, isolation_level="", detect_types=0, timeout=None, *args, **kwargs):
         self.db = c_void_p()
         if sqlite.sqlite3_open(database, byref(self.db)) != SQLITE_OK:
             raise OperationalError("Could not open database")
+        if timeout is not None:
+            timeout = int(timeout * 1000) # pysqlite2 uses timeout in seconds
+            sqlite.sqlite3_busy_timeout(self.db, timeout)
 
         self.text_factory = lambda x: unicode(x, "utf-8")
         self.closed = False