Problem with pyodbc threading

[Rudd]

Hello,

I'm trying to use pyodbc for database connections in a threaded web
app, but our queries are running serially. To be sure, I made up a
little test and ran it in the Python REPL:

- Make two connections
- Define one function to get a cursor and run a multi-second query on
connection 1
- Define another function to get a cursor and run the same query on
connection 2
- Start one thread running function 1
- Start another thread running function 2
- Join thread 1
- Join thread 2

After adding in some print statements, it is clear that both threads
start around the same time, but thread 2 will not acquire a cursor
until thread 1 has finished executing its query. I've tried this with
two different ODBC drivers (one for Vertica and one for PostgreSQL),
and the problem happens in the same way for each. I've also tried
psycopg2 to connect to Postgres, but there is no such problem there;
each query runs in parallel.

Because psycopg2 runs fine, I'm not sure what the problem is here.
Could this be a bug in pyodbc?

Thanks,
Rudd

[Michael Kleehammer]

Somehow I completely missed this discussion.  Someone pointed it out to me recently, so I'll go ahead and post an initial response for others.

There isn't anything that I know of in pyodbc that would cause this issue.  Obviously Python threads running Python code do not run at the same time due to the "global interpreter lock" (GIL).  However, pyodbc releases the lock before calling most database APIs.  For example, in cursor.cpp you'll see:

Py_BEGIN_ALLOW_THREADS

ret = SQLExecute(cur->hstmt);

Py_END_ALLOW_THREADS

The THREADS macros release the lock so other Python threads can execute.

Any code before the actual cursor.execute call would not be in parallel, so it is possible that the two queries are not being reached at the same time?

[Rudd Zwolinski]

Thought I'd follow up one last time on this, since I got the answer to
the problem sent to me in a private email. I ended up using a
different solution (using JDBC connections through Jython), but as
pointed out in a discussion here:

http://lists.ibiblio.org/pipermail/freetds/2009q4/025441.html

...I probably would have been fine if I had added 'Threading = 1' to
my odbcinst.ini file. I haven't tested it, but it sounds quite
plausible.

Best,
Rudd