IMPLICIT TRANSACTIONS

Sam 1,476 Reputation points
2023-11-23T16:42:14.02+00:00

Hi All,

In our environment we have bunch of vendor specific db's where it is using SET IMPLICIT_TRANSACTIONS ON setting and it is causing a lot of BLOCKING. especially for long running adhoc batches with multiple statements and transactions,keeping the transactions open and holding locks for longer duration.

So, the issue started when one of the developer came to us and reported that a stored when invokved from the front-end application is causing a lot of blocking but when the same piece of code ran from SQL Server Management Studio, runs fine without any issues.

We have identified that using sp_whoisactive with @get_transaction_info = 1 [implicit_tran] column. observed, SSMS SPID[implicit_tran] value is OFF and for the application SPID [implicit_tran] value is ON. so, we they made the change (IMPLICIT_TRANSACTIONS OFF) and it fixed blocking.

So would like to know, is this common practice of using IMPLICIT_TRANSACTIONS ON and running the transactions?

Why did Microsoft give this option IMPLICIT_TRANSACTIONS. Why can't it be removed or turned OFF or does it exist to support backward compatibility?

Turning OFF IMPLICIT_TRANSACTIONS is good thing or is there any real use cases of why we need to use IMPLICIT_TRANSACTIONS ON.

Regards,

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,274 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106K Reputation points MVP
    2023-11-23T22:01:08.1566667+00:00

    We have discussed this before, haven't we?

    SET IMPLICIT_TRANSACTIONS ON is there because it is the ANSI standard. Most other engines work this way: when you run a SELECT, INSERT, UPDATE or for that matter a DDL statement that starts a transaction if there is no user-defined transaction. SQL Server (and Sybase) is the odd one out by defaulting to auto-commit. That is, if there is no user-defined transaction, each statement is its own transaction.

    No, I am not saying that implicit transactions are a good thing. I recall that many many years ago when I worked with a product where auto-commit was not even an option, we quickly identified that it was best practice to always have the corresponding to BEGIN TRANSACTION. But it is the ANSI standard.

    Note that if you have a piece of work that needs to be atomic, there is no difference between doing:

    BEGIN TRANSACTION
    -- Many statements here
    COMMIT TRANSACTION
    

    or doing

    SET IMPLICIT_TRANSACTIONS ON
    -- Many statements here
    COMMIT TRANSACTION
    

    There will just as many locks and just as much blocking in both cases.

    So both are bad then? Stop, read what I wrote above "a piece of work that needs to be atomic". That is, if you have a suite of statements that needs to permitted as a whole, you need a transaction, and this always trumps strife with blocking etc. The blocking and poor performance may need to be dealt with, but not by removing transactions unless you can identify that the work can be split up in several smaller transactions. But blocking and poor performance is often due to poorly written queries and/or insufficient indexes.

    So, the issue started when one of the developer came to us and reported that a stored when invokved from the front-end application is causing a lot of blocking but when the same piece of code ran from SQL Server Management Studio, runs fine without any issues.

    This is often due to parameter sniffing. I explain this in my article Slow in the Application - Fast in SSMS?, including why it seems to run faster in SSMS. Quick version: run SET ARTITHABORT OFF in SSMS before you run the SP. Quite likely, it will run slow now. And, no, SET ARITHABORT ON/OFF as such has zero effect on performance.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful