Connection keeps open transaction (open_transaction_count) after connection.commit - works as intended?

[qamfmladitsch]

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 > 0

Is 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.

[github-actions]

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]

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 a
multi-statement session-setup batch (SET ...; EXEC sp_set_session_context ..., executed
with use_prepare=False), then a prepared parameterized UPDATE against an application
table, then calls connection.commit(), and finally connection.close() (the connection
is 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 shows
the uncommitted transaction is the connection's first one:

    SELECT s.session_id, s.login_time, at.name, at.transaction_begin_time
    FROM sys.dm_exec_sessions s
    JOIN sys.dm_tran_session_transactions st ON st.session_id = s.session_id
    JOIN sys.dm_tran_active_transactions at ON at.transaction_id = st.transaction_id
    WHERE s.program_name = 'MSSQL-Python' AND s.status = 'sleeping'

Every affected session returns transaction_begin_time equal to login_time to the
second, and sys.dm_exec_sessions.last_request_start_time matches too — i.e. the implicit
transaction 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, the
oldest 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 hold
on log truncation / version store) persist indefinitely.

The IF @@TRANCOUNT > 0 COMMIT TRANSACTION workaround from the issue description works
for us as well. Happy to provide more detail if useful.

[KornelijusS]

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 open
transactions to sys.dm_tran_database_transactions returns no rows for any of them —
they have never written a single log record in any database:

  SELECT st.session_id, at.transaction_begin_time,
          dt.database_transaction_log_record_count
   FROM sys.dm_tran_session_transactions st
   JOIN sys.dm_tran_active_transactions at ON at.transaction_id = st.transaction_id
   LEFT JOIN sys.dm_tran_database_transactions dt ON dt.transaction_id = st.transaction_id
   JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
   WHERE s.program_name = 'MSSQL-Python' AND s.status = 'sleeping';

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_desc stays
LOG_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_time equals the session's
last_request_start_time to the second: the sequence execute → 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 there
begins 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]

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.