[pysqlite] APSW not releasing global interpreter lock for call to sqlite3_prepare_v2

Hi,

We have been having problems with APSW deadlocking and
eventually raising a BusyError.  It looks to me like the problem
is that execute() doesn't release the global interpreter lock when
it gets a statement cache miss and has to call sqlite3_prepare_v2().

The attached script demonstrates the problem.  It uses two threads.
The main thread executes a SELECT statement just to show that
it works, sleeps for half a second, and then tries to execute the
same SELECT statement again while a helper thread is holding
an exclusive lock.  Each SELECT statement is executed using a
new Connection, defeating the statement cache.  The busy timeout
is set to 5 seconds.

What happens is that the second SELECT blocks as expected,
but then the helper thread also stops executing and doesn't
release the exclusive lock until the main thread eventually times
out with a BusyError from sqlite_prepare_v2().

Here is the output I get from the script:

$ python deadlock.py
Calling execute...
...execute returned.
1
2
Calling execute...
Traceback (most recent call last):
   File "deadlock.py", line 72, in <module>
     main()
   File "deadlock.py", line 64, in main
     task_A()
   File "deadlock.py", line 35, in task_A
     c = db.execute('select * from test')
   File "deadlock.py", line 22, in execute
     return self.cursor.execute(statements, bindings)
   File "apsw.c", line 4619, in APSWCursor_execute.sqlite3_prepare_v2
apsw.BusyError: BusyError: database is locked
3
4
$

The traceback appears only after a 5-second delay.  If you look for
"print 1", "print 2", "print 3", and "print 4" in the code, you will  
see that
the helper thread spends that entire time blocked in a call to  
time.sleep(1.0).

The behavior is identical with 3.3.13-r1 and the current svn trunk.

If I bracket the call to sqlite_prepare_v2() in statementcache.c with
Py_BEGIN_ALLOW_THREADS and Py_END_ALLOW_THREADS,
the problem goes away.  That is not to claim that this is necessarily
a complete and correct fix.

Ken Rimey

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite

import os
import time
from threading import Thread

from apsw import Connection

PATH = 'deadlock.db'
TIMEOUT_SECONDS = 5

class DB(object):
    def __init__(self, path=PATH):
        self.connection = Connection(path)
        self.connection.setbusytimeout(TIMEOUT_SECONDS * 1000)
        self.cursor = self.connection.cursor()

    def close(self):
        self.cursor.close()
        self.connection.close()

    def execute(self, statements, bindings=()):
        return self.cursor.execute(statements, bindings)

def init():
    db = DB()
    try:
        db.execute('create table test (data)')
    finally:
        db.close()

def task_A():
    db = DB()
    try:
        print 'Calling execute...'
        c = db.execute('select * from test')
        print '...execute returned.'
        results = list(c)
        assert len(results) == 0
    finally:
        db.close()

def task_B():
    db = DB()
    try:
        print 1
        db.execute('begin exclusive')
        print 2
        time.sleep(1.0)
        print 3
        db.execute('commit')
        print 4
    finally:
        db.close()

def main():
    init()

    task_A()

    thread = Thread(target=task_B)
    thread.start()
    time.sleep(0.5)

    task_A()

if __name__ == '__main__':
    try:
        os.remove(PATH)
    except OSError:
        pass

    main()