Repository navigation
Connection keeps open transaction (open_transaction_count) after connection.commit - works as intended? #754
Description
Hello,
when using mssql-python we observed that it does not seem to close/commit all transactions.
For example with this script:
with mssql_python.connect(conn_string) as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM People")
print("wait here with debugger")Before the application exits we see open transactions with this:
SELECT
s.session_id,
s.login_time,
s.host_name,
s.program_name,
s.login_name,
s.last_request_end_time,
c.most_recent_sql_handle,
s.open_transaction_count,
r.*
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c
ON c.session_id = s.session_id
WHERE s.open_transaction_count > 0Is this intended behavior? The opened transaction does not seem to block any resources.
When doing a manual commit cursor.execute("IF @@TRANCOUNT > 0 COMMIT TRANSACTION") the transactions are closed properly before the application exits.
Activity
github-actions commentedon Sep 4, 2026
Hi qamfmladitsch, 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!
KornelijusS commentedon Sep 7, 2026
We're hitting what looks like exactly this on mssql-python 1.13.0 (Linux containers,
python:3.14-slim, SQL Server, pooling left at its default of enabled), and can add
server-side evidence.
Pattern: a service opens a connection (
autocommit=False, the default), runs amulti-statement session-setup batch (
SET ...; EXEC sp_set_session_context ..., executedwith
use_prepare=False), then a prepared parameterizedUPDATEagainst an applicationtable, then calls
connection.commit(), and finallyconnection.close()(the connectionis opened and closed per operation; pooling keeps it alive).
Observed: the session stays alive in the pool (expected), but sleeping with
open_transaction_count = 1— for days, until the process exits. Joining the DMVs showsthe uncommitted transaction is the connection's first one:
Every affected session returns
transaction_begin_timeequal tologin_timeto thesecond, and
sys.dm_exec_sessions.last_request_start_timematches too — i.e. the implicittransaction opened by the connection's first batch was never ended server-side even though
connection.commit()was called and returned without error. We had six such sessions, theoldest sleeping for ~3 days, each pinning
user_transaction.Since these are parked pooled connections that may never be reused, the deferred
SQL_ATTR_RESET_CONNECTION-style cleanup never runs, so the transactions (and their holdon log truncation / version store) persist indefinitely.
The
IF @@TRANCOUNT > 0 COMMIT TRANSACTIONworkaround from the issue description worksfor us as well. Happy to provide more detail if useful.
KornelijusS commentedon Sep 7, 2026
Follow-up with a more precise diagnosis after digging further — the picture is better
(and narrower) than my previous comment suggested.
The leaked transactions are empty, and
commit()actually works. Joining the opentransactions to
sys.dm_tran_database_transactionsreturns no rows for any of them —they have never written a single log record in any database:
We also verified row-by-row that every INSERT/UPDATE/EXEC those sessions ran was durably
committed. So no data is at risk — consistent with the OP's observation that nothing is
blocked. The sessions also don't pin log truncation (
log_reuse_wait_descstaysLOG_BACKUP), since an empty transaction has no begin LSN.The phantom transaction is opened by the close/park path, after the successful commit.
On every affected session,
transaction_begin_timeequals the session'slast_request_start_timeto the second: the sequenceexecute → Connection.commit() → cursor.close() → Connection.close()(autocommit=False, pooling at its default of enabled)leaves a fresh, empty, never-ended transaction on the parked connection. It affects every
statement type we use (direct batches, prepared statements, RPC/EXEC). Connections that get
reused from the pool are cleaned up by the deferred connection reset — only parked
connections that never get checked out again expose it, which is why the zombies cluster
around process startup and concurrency bursts.
So the bug looks localized to whatever the close/park sequence does after
SQLEndTran(the pre-park rollback, or statement cleanup such as
sp_unprepare) — something therebegins a new implicit transaction that is never ended before the connection is parked.
Observed on 1.13.0, Linux (python:3.14-slim) against SQL Server. Happy to test a fix or
provide more traces.
sumitmsft commentedon Sep 10, 2026
Hi qamfmladitsch and KornelijusS -
Thank you for the detailed report and follow-up investigation. We reproduced the behavior and confirmed this is an issue in the pooled connection close path.
commit()correctly persists the application’s changes; however, the physical connection can be parked in the pool with a new, empty transaction, causing open_transaction_count to remain 1. We are preparing a fix to ensure pooled connections are transaction-clean before being parked.We’ll update this issue when the fix is available.
Merge main into pooled transaction fix branch
Merge main into pooled transaction fix branch