Repository navigation
Description
Describe the bug
#859 bounds pooled checkout on an unresponsive socket (#854). That fixes the unbounded hang with default settings.
However, during checkout the driver now temporarily replaces the connection's SQL_ATTR_CONNECTION_TIMEOUT with the login timeout. Callers who had already bounded the check with a short attrs_before={SQL_ATTR_CONNECTION_TIMEOUT: n} get a much longer wait after upgrading to 1.17.0. The only way to shorten it is to lower the login timeout, which also limits real logins.
No exception is raised: the request succeeds on a new connection, but only after 3× the login timeout.
One pooled connection. Its traffic is silently dropped with iptables -j DROP (no FIN/RST), then the next connect() + query is timed. Two runs each.
| Version | timeout (login) |
SQL_ATTR_CONNECTION_TIMEOUT = 1 |
not set |
|---|---|---|---|
| 1.16.0 | 15 | 3.0 s / 3.0 s | blocked > 60 s (both) |
| 1.16.0 | 3 | 3.0 s / 3.0 s | blocked > 60 s (both) |
| 1.17.0 | 15 | 45.1 s / 45.0 s | 45.1 s / 45.1 s |
| 1.17.0 | 3 | 9.0 s / 9.0 s | 9.1 s / 9.0 s |
On 1.17.0 the wait is 3× the login timeout, and an explicit connection timeout makes no difference.
Driver log for 1.17.0 with timeout=3. The whole wait is the single isolation-level call during reset. The replacement login and the discard of the dead connection are immediate.
09:33:33.231 Setting transaction isolation level to READ COMMITTED
09:33:42.244 Failed to set transaction isolation level (ret=-1). Marking as dead.
09:33:42.244 Connecting to database
09:33:42.269 Disconnecting from database
To reproduce
Complete script below. It uses only mssql_python, and needs Linux and root for iptables. Run one scenario per process, because pooling is process-wide.
REPRO_SERVER=host,1433 REPRO_SA_PASSWORD=... python repro.py idle 15 1 # idle connection dies; connection timeout 1
REPRO_SERVER=host,1433 REPRO_SA_PASSWORD=... python repro.py idle 15 0 # same, connection timeout not set
REPRO_SERVER=host,1433 REPRO_SA_PASSWORD=... python repro.py inuse 15 1 # socket dies mid-request
repro.py
"""Standalone repro: only mssql_python. One scenario per process; prints one JSON line.
usage: repro.py idle|inuse <login timeout> <connection timeout, or 0 for not set>
Needs root (iptables) on Linux."""
import json, os, subprocess, sys, threading, time
import mssql_python
scenario, login_timeout, connection_timeout = sys.argv[1], int(sys.argv[2]), int(sys.argv[3])
SERVER = os.environ.get("REPRO_SERVER", "localhost,1433")
PASSWORD = os.environ["REPRO_SA_PASSWORD"]
CAP = 60.0
PORT = "select client_tcp_port from sys.dm_exec_connections where session_id = @@spid"
rules = []
mssql_python.pooling(max_size=100, idle_timeout=600)
def connect():
extra = {"attrs_before": {mssql_python.SQL_ATTR_CONNECTION_TIMEOUT: connection_timeout}} if connection_timeout else {}
conn = mssql_python.connect(Server=SERVER, UID="sa", PWD=PASSWORD, Encrypt="yes", TrustServerCertificate="yes",
timeout=login_timeout, autocommit=False, **extra)
conn.timeout = 15 # query timeout
return conn
def query(conn, sql):
cursor = conn.cursor(); cursor.execute(sql); return cursor.fetchone()[0]
def request(sql=PORT):
with connect() as conn: # commits and returns the connection to the pool
return query(conn, sql)
def blackhole(port):
for chain, match in (("OUTPUT", "--sport"), ("INPUT", "--dport")):
rule = [chain, "-p", "tcp", match, str(port), "-j", "DROP"]
subprocess.run(["iptables", "-A", *rule], check=True); rules.append(rule)
def timed(fn):
box = {}
def run():
started = time.monotonic()
try:
box["value"] = fn()
except Exception as exc:
box["error"] = type(exc).__name__
box["seconds"] = round(time.monotonic() - started, 1)
worker = threading.Thread(target=run, daemon=True); worker.start(); worker.join(CAP)
return {"seconds": f"blocked > {CAP:.0f}"} if worker.is_alive() else box
out = {"driver": mssql_python.__version__, "scenario": scenario, "login_timeout": login_timeout,
"connection_timeout": connection_timeout or "not set"}
if scenario == "idle":
dead = request(); blackhole(dead); time.sleep(2)
nxt = timed(request)
out["next_request_s"] = nxt["seconds"]; out["error"] = nxt.get("error"); out["got_new_connection"] = nxt.get("value") not in (None, dead)
else:
def dies_mid_request():
phases = {}
conn = connect()
try:
with conn:
blackhole(query(conn, PORT))
started = time.monotonic()
try:
query(conn, "select 1")
except Exception as exc:
phases["query_error"] = type(exc).__name__
phases["query_s"] = round(time.monotonic() - started, 1)
except Exception as exc:
phases["close_error"] = type(exc).__name__
return phases
first = timed(dies_mid_request)
out["request_s"] = first["seconds"]; out.update(first.get("value") or {})
if isinstance(first["seconds"], float):
nxt = timed(lambda: request("select 1")); out["next_request_s"] = nxt["seconds"]
for rule in rules:
subprocess.run(["iptables", "-D", *rule], check=False)
print(json.dumps(out), flush=True)
os._exit(0) # a still-blocked worker thread must not turn the exit into a crashExpected behavior
An explicitly set SQL_ATTR_CONNECTION_TIMEOUT keeps bounding the checkout validation, as it did on 1.16.0 (3 s in the table above).
Any one of these would resolve it:
- At checkout, use the smaller of the login timeout and an explicitly set
SQL_ATTR_CONNECTION_TIMEOUT. - Expose a separate validation timeout, for example on
pooling(...). - At minimum, document that the effective wait is about 3× the timeout.
Further technical details
Python version: 3.12
SQL Server version: SQL Server 2022 (container)
Operating system: Docker container, Debian trixie, aarch64
mssql-python: 1.16.0 and 1.17.0, bundled ODBC Driver 18. Pooling on, autocommit=False.
Additional context
Why it matters: the login timeout has to be generous enough for a real login under load or failover, while the check on an existing socket should be short. Tying the two together forces a choice between slow recovery from a dead pooled connection and failed logins.
Related, a socket that dies mid-request: the README notes that returning a connection to the pool is not bounded by this fix. Measured with login timeout 15 and query timeout 15, when the socket dies while the connection is checked out. Two runs each; the results are the same on 1.16.0 and 1.17.0.
SQL_ATTR_CONNECTION_TIMEOUT |
Result |
|---|---|
| 1 | the query fails after 17.0 s; the next request is immediate |
| not set | blocked > 60 s |
So a short explicit connection timeout is still needed for this case, which makes it more important that checkout respects it.
Activity
github-actions commentedon Nov 12, 2026
Hi Dimitris Lygkonis (@xyres137), thank you for opening this issue!
Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.
If you have additional information to share, please feel free to update the issue.
Thank you for your patience!
jahnvi480 commentedon Nov 13, 2026
Dimitris Lygkonis (@xyres137) Thanks for raising this issue... I have created a PR to fix this. Once this PR is merged tpo main I'll inform you accordingly
FIX: Respect explicit connection timeout during pooled checkout (#897)
jahnvi480 commentedon Nov 13, 2026
Pr has been merged closing the issue