Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error on attempting to use the same connection in multiple threads. #31

Closed
rogerbinns opened this Issue Dec 29, 2013 · 9 comments

Comments

Projects
None yet
1 participant
@rogerbinns
Copy link
Owner

rogerbinns commented Dec 29, 2013

From amicitas on October 24, 2008 13:05:39

What steps will reproduce the problem? 1. Attempt to simultaneously use the same connection in two threads. What is the expected output? What do you see instead? It would be very nice if an error was raised if this is attempted. At this
point the application just hangs with no error output. What version of the product are you using? On what operating system? apsw 3.5.9- r2 , python 2.5 & 2.6, windows xp sp3 Please provide any additional information below. The documentation does clearly state that a connection cannot be used in
more than one thread. If it is attempted the application just hangs. Both
threads stop as well as the main program. I would be nice if an error was
thrown as it is if the cursor is used in two threads.

-- amicitas

Original issue: http://code.google.com/p/apsw/issues/detail?id=31

@ghost ghost assigned rogerbinns Dec 29, 2013

@rogerbinns

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

Labels: -Priority-Medium Priority-Critical

@rogerbinns

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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

This comment has been minimized.

Copy link
Owner Author

rogerbinns commented Dec 29, 2013

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!)

@rogerbinns rogerbinns closed this Dec 29, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.