Thanks for the response. I duplicated the original problem with the
following test:
$ cat /f/t.py
import sqlite3
c1 = sqlite3.connect("db", timeout=5.0)
try:
c1.execute("create table person(lastname, firstname)")
c1.execute("insert into person values('newhook', 'matthew')")
c1.commit()
except sqlite3.OperationalError, e:
pass
c1 = sqlite3.connect("db", timeout=5.0, isolation_level="immediate")
c2 = sqlite3.connect("db", timeout=5.0, isolation_level="immediate")
c1.execute("insert into person values('newhook', 'matthew')")
c2.execute("insert into person values('newhook', 'matthew')")
While I understand why this deadlocks without BEGIN IMMEDIATE (of
course, this would always deadlock -- but this deadlocks immediately
as opposed to after 5 seconds). The documentation, imo, doesn't really
make it obvious:
timeout - When a database is accessed by multiple connections, and one
of the processes modifies the database, the SQLite database is locked
until that transaction is committed. The timeout parameter specifies
how long the connection should wait for the lock to go away until
raising an exception. The default for the timeout parameter is 5.0
(five seconds).
This isn't true of course as it depends on the isolation.
I verified with the next test that things do work correctly:
$ cat t1.py
import sqlite3
con = sqlite3.connect("db", timeout=5.0)
try:
con.execute("create table person(lastname, firstname)")
con.execute("insert into person values('newhook', 'matthew')")
con.commit()
except sqlite3.OperationalError, e:
pass
for row in con.execute("select * from person"):
print row
import threading
import thread
class TestThread(threading.Thread):
def __init__(self):
threading.Thread.__init__(self)
self._stop = False
self._lock = thread.allocate_lock()
self._count = 0
def run(self):
while True:
self._lock.acquire()
if self._stop:
self._lock.release()
return
self._lock.release()
con = sqlite3.connect("db", timeout=5.0,
isolation_level="immediate")
con.execute("insert into person values('newhook',
'matthew')")
con.commit()
time.sleep(0.1)
self._count = self._count + 1
def count(self):
return self._count
def stop(self):
self._lock.acquire()
self._stop = True
self._lock.release()
t1 = TestThread()
t2 = TestThread()
t1.start()
t2.start()
import time
time.sleep(2)
t1.stop()
t2.stop()
t1.join()
t2.join()
print "t1._count=%d" % t1.count()
print "t2._count=%d" % t2.count()
Regards, Matthew
On Nov 2, 7:53 pm, Gerhard Häring <[EMAIL PROTECTED]> wrote:
> On Nov 1, 3:13 pm, Matthew Newhook <[EMAIL PROTECTED]> wrote:
>
> > I posted this message in the pylons group but as of yet have received
> > no response.
>
> I saw it there and followed here. And subscribed while I'm at it - I'm
> playing with SQLAlchemy myself recently.
>
> >http://groups.google.com/group/pylons-discuss/browse_frm/thread/093ec...
>
> > I've been looking into this problem a little more. From my reading of
> > the SQLite documentation there should be a 5 second timeout by default
> > (I'm using pysqlite 2.3.2). [...]
>
> Please update to the latest pysqlite (version 2.3.5). I've improved
> pysqlite's concurrency with 2.3.4 by deferring the implicit BEGIN/
> COMMITs.
>
> This was the problem and the
> patch:http://initd.org/tracker/pysqlite/ticket/184
>
> -- Gerhard
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
