Post reply

IMPLICIT TRANSACTIONS

  • November 23, 2023 at 4:41 pm

    Go to Answer

    #4333179

    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 compatability?

    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

  • November 23, 2023 at 5:38 pm

    Answer

    #4333210

    I am not sure what question/problem is.

    A few basic points to note:

    1. Oracle effectively works as SET IMPLICIT_TRANSACTIONS ON so third party applications, which target multiple dbs, tend to work like this.

    2. If the long transaction is required, SET IMPLICIT_TRANSACTIONS OFF could mess up a rollback.

    3. If a stored procedure is being called from the application with SET IMPLICIT_TRANSACTIONS ON, I would not just SET IMPLICIT_TRANSACTIONS OFF. Connection pooling is likely to be used so there could be all sorts of unintended side effects! At a minimum you should SET IMPLICIT_TRANSACTIONS ON at the end of the procedure.

    IF (@@OPTIONS & 2 = 0)
        SET IMPLICIT_TRANSACTIONS ON;

    Ideally, I would leave IMPLICIT_TRANSACTIONS alone and have the following where you know it is safe to commit:

    IF (@@OPTIONS & 2 <> 0)
        COMMIT;

    • This reply was modified 1 year, 1 month ago by  Ken McKelvey.
    • This reply was modified 1 year, 1 month ago by  Ken McKelvey.
    • This reply was modified 1 year, 1 month ago by  Ken McKelvey.
    • This reply was modified 1 year, 1 month ago by  Ken McKelvey.

  • November 23, 2023 at 6:02 pm

    #4333221

    Just to summarize:

    https://www.brentozar.com/archive/2018/02/set-implicit_transactions-one-hell-bad-idea/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 24, 2023 at 6:00 am

    #4333444

    Thank you Ken and Jeff

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply