Share via

Why are ODBC statements still being registered as commits in SQL Server even though autocommit is off?

Alex 0 Reputation points
2024-11-06T17:01:30.7866667+00:00

Hi,

When executing (INSERT/UPDATE) statements through an ODBC driver for SQL Server, with SQL_ATTR_AUTOCOMMIT set to OFF on the connection, the autocommit behavior does reflect this, in practice. I.e., the data being updated is not available on the server until the 'real' commit happens (calling SQLEndTran).

However, when profiling the transaction (using SQL Server Profiler), each statement is still being registered a commit. The EventClass is SQLTransaction and the EventSubClass column shows '1 - Commit'.
We are not seeing any improvement in transactional performance with autocommit on or off, could these erroneous commits after each statement be the cause?

Thanks,

Alex

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,133 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 38,456 Reputation points
    2024-11-07T06:46:11.9233333+00:00

    Hi @Alex,

    There are several clarifying points to understand:

    • When the transaction mode is implicit, no unseen BEGIN TRANSACTION is issued if @@trancount > 0 already. However, any explicit BEGIN TRANSACTION statements still increment @@TRANCOUNT.
    • When your INSERT statements and anything else in your unit of work is finished, you must issue COMMIT TRANSACTION statements until @@TRANCOUNT is decremented back down to 0. Or you can issue one ROLLBACK TRANSACTION.
    • SELECT statements that do not select from a table do not start implicit transactions. For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.
    • Implicit transactions may unexpectedly be ON due to ANSI defaults. For details see SET ANSI_DEFAULTS (Transact-SQL). IMPLICIT_TRANSACTIONS ON is not popular. In most cases where IMPLICIT_TRANSACTIONS is ON, it is because the choice of SET ANSI_DEFAULTS ON has been made.
    • The SQL Server Native Client OLE DB Provider for SQL Server, and the SQL Server Native Client ODBC driver, automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.