Bug: Arrow ODBC SQL Server transaction begin drops committed DML visibility

[cofin]

Summary

When using sqlspec.adapters.arrow_odbc.ArrowOdbcConfig against SQL Server, DML executed inside SQLSpec’s explicit transaction flow appears visible on the same connection immediately after ArrowOdbcDriver.dispatch_execute(), but is not visible from the next SQLSpec session after driver.commit() and session close.

This showed up while adding a Litestar Queues SQLSpec Arrow ODBC SQL Server backend. The queue backend does:

1. driver.begin()
2. driver.execute(insert_statement)
3. driver.commit()
4. opens a new session and selects the inserted row by id

The insert reports success and the row is visible via SELECT COUNT(*) on the same Arrow ODBC connection immediately after connection.execute(...). After commit/close, the next session sees zero rows.

Environment

• sqlspec==0.52.0
• arrow-odbc==10.4.2
• pyarrow==24.0.0
• Python 3.14.4
• SQL Server 2022 via pytest-databases (mcr.microsoft.com/mssql/server:2022-latest)
• ODBC Driver 18 for SQL Server

Evidence

With the stock SQLSpec Arrow ODBC MSSQL transaction path, a simple enqueue/claim contract fails because the row inserted in one session is gone in the next session:

SELECT COUNT(*) AS row_count FROM [litestar_queue_task_arrow_odbc_mssql_df26a59cd1] immediately after INSERT on the same connection -> 1
SELECT COUNT(*) AS row_count FROM [litestar_queue_task_arrow_odbc_mssql_df26a59cd1] in the next SQLSpec session -> 0

A local monkeypatch that makes ArrowOdbcDriver.begin() a no-op for MSSQL lets the insert persist and the immediate enqueue/claim contract pass:

orig_begin = ArrowOdbcDriver.begin

def patched_begin(self):
    if getattr(self, "_dialect", None) == "mssql":
        return None
    return orig_begin(self)

That suggests the issue is tied to ArrowOdbcDriver.begin() sending raw BEGIN TRANSACTION for SQL Server while commit() calls the arrow-odbc connection commit() API. Either SQLSpec should not issue raw BEGIN TRANSACTION for Arrow ODBC SQL Server, or begin/commit/rollback need to use a consistent transaction mechanism for this driver.

Expected

DML executed between driver.begin() and driver.commit() should be visible to a new session after commit.

Actual

DML is visible on the same connection before commit/close but not visible from the next session after commit.