Error on attempting to use the same connection in multiple threads. about apsw

[rogerbinns]

From rogerbinns on October 24, 2008 14:42:43

I apologize for the documentation inaccuracy. Before SQLite 3.5 you had to use the
Connection in the same thread it was created in. APSW had code to detect cross
thread use and would raise an exception. This restriction was removed from SQLite
3.5 and also from APSW. I updated the documentation in several places but missed
some. (There is a remaining restriction that you can't use the same object in
multiple threads if the GIL is released for that object but actually doing this is
detected and an exception raised anyway.)

I am in the middle of moving the documentation to the Sphinx tool which results in
far better output, cross referencing etc and am also reviewing and updating all of it.

That said, I cannot reproduce this issue. The test suite already does all sorts of
funky threading stuff which would have caught this problem. I added an additional
specific test in http://code.google.com/p/apsw/source/detail?r=293 Can you post code to reproduce the problem?

Owner: rogerbinns
Labels: OpSys-Windows

[rogerbinns]

From amicitas on October 24, 2008 16:28:11

I'll try to reproduce this over the weekend. The place where it was happening was
pretty deep in my code and I have already restructured it to use a different
connection for each thread. I need to put something together that is simpler for you.

It was happening when I tried to execute a query from one thread while another
thread, using the same connection but a different cursor, was either in the middle of
a query or iterating over the cursor.

I did just upgrade from python 2.5 to 2.6 and I noticed (just now) that I had not
actually installed apsw for the 2.6 installation. My program must have been using
some code compiled with 2.5. I don't know if this may have caused the problem that I
ran into.

-- amicitas

[rogerbinns]

From rogerbinns on October 24, 2008 16:46:09

You certainly can't use an extension compiled for Python 2.5 with Python 2.6 under
Windows. This is what happens.

Python 2.6 ( r26 :66721, Oct 2 2008, 11:35:03) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.

import apsw
Traceback (most recent call last):
File "", line 1, in
ImportError: Module use of python25.dll conflicts with this version of Python.

I have added some select iteration to the test as well and still don't see any failures.

The only thing I can think of that could cause your issue that is SQLite related is
if have busy handlers that sleep for a very long time. The other alternative is if
you compiled SQLite/APSW yourself and deliberately used flags to make it multi-thread
unsafe (ie leave out all mutexes).

In general it is a good idea to share the same connection across threads as they will
share the same cache and data structures.

I'm looking forward to your code reproduction :)

[rogerbinns]

From amicitas on October 26, 2008 14:20:27

Ok I found it.

This happens when a function is defined using "createscalarfunction()". (It took me a
long time to figure out that this was the cause)

Attached is some test code that reproduces the problem for me. I have only tested
this on Python 2.6.

Let me know if you need more information.

-- amicitas

btw, what happened with my upgrade to python 2.6 was that although I upgraded, I was
still using a terminal window that I had opened before the upgrade with the path set
to the 2.5 installation

Attachment: apsw_bug.py

[rogerbinns]

From rogerbinns on October 26, 2008 15:18:13

Thank you for that. I can confirm the problem. The lockup results when Python and
SQLite have hit a deadlock (they are all waiting on mutexes). I am trying to work
out why that is happening and a fix.

It affects all operating systems. Attached is a simpler version of the code to
reproduce.

Status: Started
Labels: -OpSys-Windows OpSys-All

Attachment: issue31.py

[rogerbinns]

From rogerbinns on October 26, 2008 15:18:36

Labels: -Priority-Medium Priority-Critical

[rogerbinns]

From rogerbinns on October 26, 2008 18:08:28

I have found the cause. The issue also applies to pysqlite but pysqlite doesn't let
you use connections across threads by default so you wouldn't normally see it. Python
has a mutual exclusion lock known as the GIL (global interpreter lock). SQLite also
has one on each database (connection in apsw/pysqlite).

To run a query ultimately sqlite3_step is called. Just before calling it, the Python
GIL is released. sqlite3_step then acquires the connection mutex.

In another thread, the Python GIL is acquired and then a call is made to a sqlite3_*
function. That function then tries to acquire the connection mutex and we have a
deadlock.

If the GIL had been released in the second thread before calling the sqlite3_
function then the first thread could have continued ultimately finishing sqlite3_step
which would release the connection mutex and allow the second thread to continue.

(Registering a scalar function was a red herring. It just changed the timing enough
to make the issue very likely to happen.)

There are 3 solutions:

1. In your case use a separate connection per thread (short term workaround)
2. Change the apsw code to always release the GIL around every sqlite3_ call not just
   the major ones as is currently done
3. Do second but be more intelligent about it

Second is rather expensive since it involves releasing and acquiring a mutex on every
call. Third will still result in deadlock if any implementation mistakes are made,
unless I can come up with automated test.

[rogerbinns]

From rogerbinns on October 28, 2008 00:00:11

I took the second approach except for a few functions where it is not necessary to
due them being called from the same thread as sqlite3_step is running. Also added a
test that inspects source code to verify various sqlite and apsw library calls are
being made with correct thread guarding.

Fixed in r299 .

Status: Verified

[rogerbinns]

From rogerbinns on November 05, 2008 01:52:22

On further work I established that you can't get SQLite error messages in a
threadsafe manner due to their API design. SQLite CVS now has an additional method
that allows getting it safely. Consequently the next APSW release will require
SQLite 3.6.5 and will be released at the same time.

(Unless the SQLite 3.6.5 release is seriously delayed in which case a work around can
get the error message safely but there is no guarantee it came from the same thread!)