Multithreaded pyodbc connection

Question

I am trying to use a pyodbc connection in multiple threads. I am receieving the following error: Connection is busy with results for another command (0) (SQLExecDirectW)'). I also tried setting MultipleActiveResultSets=yes; and got the same results.

I really don't want to have to create a connection for every thread or query. I also don't want to use a lock to serialize queries from all threads.

I was first using the driver "SQL Server" and then moved to "SQL Server Native Client 11.0" with no luck.

Any suggestions?

Are you opposed to having the connection wait to be freed, before using it again in a different thread? — Joshua Schlichting, Jul 09 '18 at 16:26
@JoshuaSchlichting Yeah thats what I meant by not wanting to serialize threads with a lock. I might not have a choice though. — J. Darnell, Jul 09 '18 at 16:50
Yea I don't see what other choice you have. I don't know why your restrictions are what they are, but I'd opt for creating a new connection for each thread. — Joshua Schlichting, Jul 09 '18 at 16:52
From the names of the ODBC drivers it sounds like you're doing this on Windows, and Windows ODBC enables connection pooling by default for SQL Server drivers, so opening a separate connection for each thread may not necessarily be as expensive as you fear. — Gord Thompson, Jul 09 '18 at 23:55
@Gord that's correct. I think you are right about that. I did implement it that way and it appears to be working fine (so far). I have a "thread-safe" abstraction layer that handles the threads and connections. — J. Darnell, Jul 10 '18 at 00:18

score 2 · Accepted Answer · answered Jul 09 '18 at 17:34

Since this question asks for suggestions and not really a strong answer, here's mine:

Firstly, I'd suggest creating a new connection for each thread created.

Or, if you're okay with killing what is currently executing, try cursor.cancel() before reusing your connection.

Also, after a quick search, I find multiple references to Multiple Active Result Sets related to this issue. If your database supports it, try adding "MultipleActiveResultSets=True" to your connection string.

I hope one of these suggestions helps you get where you need to be!

score 2 · Answer 2 · answered Feb 21 '22 at 13:46

According to ODBC-documentation https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver15, the keyword you should be using is MARS_Connection=yes. Using this with pyodbc does work and solves my dual-open-cursor -issue.

Using MultipleActiveResultSets=true does not work for two reasons: using incorrect keyword and using incorrect values, as in ODBC flags are not true/false, they are yes/no.

Multithreaded pyodbc connection

2 Answers2