Running mssql-python on more than 2 threads can lead to hang/deadlocks

[saurabh500]

Threading Deadlock with 3+ Concurrent Threads

Description

The mssql-python library deadlocks when using 3 or more concurrent threads in a single process. Each thread executes a simple query in a connect-execute-disconnect cycle. The issue is reproducible consistently with 3+ threads but works correctly with 1-2 threads.

Environment

• mssql-python version: 0.13.1 (built from source)
• Python version: 3.12
• Operating System: Ubuntu 24.04 (WSL)
• SQL Server: SQL Server 2022 (tested on version 10.0.14.177)

Steps to Reproduce

1. Clone the reproduction repository:

git clone https://github.com/saurabh500/sqlclientrepros.git
cd sqlclientrepros/python/standalone

2. Install dependencies:

pip install mssql-python psutil

3. Run with 2 threads (works):

python parallel_query_runner.py \
  -c "Server=YOUR_SERVER,1433;Database=master;UID=sa;PWD=YOUR_PASSWORD;TrustServerCertificate=yes;" \
  -t 2 \
  -i 150

4. Run with 3 threads (hangs):

timeout 60s python parallel_query_runner.py \
  -c "Server=YOUR_SERVER,1433;Database=master;UID=sa;PWD=YOUR_PASSWORD;TrustServerCertificate=yes;" \
  -t 3 \
  -i 150

Expected Behavior

All threads should complete their iterations successfully, similar to the 2-thread case.

Actual Behavior

With 3 or more threads, the application hangs indefinitely. Threads appear to deadlock during database operations. The timeout command is required to terminate the hung process.

Code Reference

The minimal reproduction code is available at:
https://github.com/saurabh500/sqlclientrepros/tree/master/python/standalone

Key file: parallel_query_runner.py

Each thread executes:

1. Connect to database
2. Execute query: SELECT 1 as num, 'test' as str, GETDATE() as dt
3. Read all results
4. Close connection
5. Repeat for N iterations

Test Results

2 Threads - Success

Total Iterations:  300
Total Time:        1.122s
Avg Throughput:    267 queries/sec
Total Errors:      0

3 Threads - Hangs

Application hangs indefinitely
Threads never complete
Process must be killed manually

5 Threads - Hangs

Same behavior as 3 threads

Comparison with PyODBC

The same test pattern was implemented using PyODBC (ODBC Driver 18 for SQL Server) and works correctly with 4+ threads without hanging.

Additional Testing

Multiple Separate Processes

Running 5 separate single-threaded processes (instead of 1 process with 5 threads) works correctly:

• Each process: 1 thread, 200 iterations
• All 5 processes completed successfully
• Total time: ~0.8s per process

This confirms the issue is specific to multi-threading within a single process, not database concurrency limits.

Connection Pooling

Testing with connection pooling disabled (--disable-pooling flag) shows the same hanging behavior, indicating the issue is not related to the connection pool implementation.

Impact

This limitation prevents using mssql-python in multi-threaded applications that require 3 or more concurrent database connections, such as:

• Thread pool executors with more than 2 workers
• Concurrent batch processing applications
• Multi-threaded ETL tools

Additional Information

Full test suite and comparison with PyODBC available at:
https://github.com/saurabh500/sqlclientrepros/tree/master/python/standalone

The repository includes:

• Reproduction scripts for both mssql-python and PyODBC
• Test results and comparison data
• Resource monitoring implementation
• README with detailed usage instructions

[bewithgaurav]

Thanks for raising this @saurabh500 !
We tried to repro this but could not see this behaviour in either macOS or Linux
Below are the results from ubuntu 22.04 on a docker container
used

(ubuntuvenv) root@03a9f54a66a0:/workspace/sqlclientrepros/python/standalone# python parallel_query_runner.py -t 100 -i 1000 -v
--------------------------------------------------------------------------------
Overall Statistics:
  Total Time:        237.952s
  Total Iterations:  100000
  Total Rows:        100,000
  Total Errors:      0
  Avg Throughput:    420.25 queries/sec
  Avg Rows/sec:      420.25 rows/sec
================================================================================

Closing this issue since this seems like an inconsistent bug and we don't have a repro with the code provided on multiple OSs
Please reopen if this resurfaces.

[saxostsi]

I'm on windows and using trio to run ~6 queries at the same time. Using pyodbc, they all run simultaneously. Using mssql-python, it looks like only 2 connections can be allocated at the same time from my logs. I think it's something related to this issue.

[saurabh500]

I'm on windows and using trio to run ~6 queries at the same time. Using pyodbc, they all run simultaneously. Using mssql-python, it looks like only 2 connections can be allocated at the same time from my logs. I think it's something related to this issue.

@saxostsi it would be good to open a new issue with your repro. My repro was invalidated by the team (and rightly so). I also saw notable improvements in my repro with the v1 of the driver.

Having said this if you're experiencing problems then please start a new issue.

Cc @sumitmsft and @bewithgaurav