Skip to content

Pooled checkout ignores an explicit SQL_ATTR_CONNECTION_TIMEOUT and waits 3x the login timeout (regression in 1.17.0, follow-up to #854) #893

Closed

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 crash

Expected 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:

  1. At checkout, use the smaller of the login timeout and an explicitly set SQL_ATTR_CONNECTION_TIMEOUT.
  2. Expose a separate validation timeout, for example on pooling(...).
  3. 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

  1. github-actions commented on Nov 12, 2026

    @github-actions

    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!

  2. jahnvi480 commented on Nov 13, 2026

    @jahnvi480
    Contributor

    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

  3. added
    bugSomething isn't working
    triage doneIssues that are triaged by dev team and are in investigation.
    area: api-compliancePython API behavior and typing: DB-API 2.0, exceptions, type stubs, new APIs.
    and removed
    triage neededFor new issues, not triaged yet.
    on Nov 13, 2026
  4. jahnvi480 commented on Nov 13, 2026

    @jahnvi480
    Contributor

    Pr has been merged closing the issue

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

area: api-compliancePython API behavior and typing: DB-API 2.0, exceptions, type stubs, new APIs.bugSomething isn't workinginADOtriage doneIssues that are triaged by dev team and are in investigation.under development

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions