MPLICIT_TRANSACTIONS

Sam 1,476 Reputation points
2023-10-18T05:22:25.5833333+00:00

Hi All,

Today, we came across a blocking scenario. A stored proc is running fine if it is executed from SQL Server Management Studio. But when executed from Front End App, that stored proc is causing heavy blocking and it runs forever. It was taking 3000 locks.One difference, I have checked is, when it is executed from SSMS, the implicit_transaction is OFF. When it is executed from App, IMPLICIT_TRANSACTIONS is ON.

Does this setting cause blocking? I have used sp_whoisactive to determine that setting is ON/OFF.

--transaction log write info
EXEC sp_WhoIsActive
@Output_Column_List = '[dd hh:mm:ss.mss][database%][session_id][status][cpu][reads][sql_%][login_name][tran_log_writes][implicit_tran]',
@get_transaction_info = 1
,@filter_type = 'session', @filter = '454'

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.
14,011 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 112.7K Reputation points MVP
    2023-10-18T21:55:02.8233333+00:00

    When the setting IMPLICIT_TRANSACTONS is in effect, any DML statement, including SELECT, as well as DDL etc will start a user-defined transaction. While sounds very funny in the SQL Server world, this is the behaviour with most database products and it is also the ANSI standard.

    I would guess that the application uses some API to start a transaction. I've noticed that some Transaction APIs rely on SET IMPLICIT_TRANSACTIONS ON rather than issuing an explicit BEGIN TRANSACTION.

    Obviously, if you have a user-defined transaction rather than having auto-committed statements, this means that locks will be held longer. But I don't really think this is the main explanation. But you could try this:

    BEGIN TRANSACTION
    EXEC ThisSP
    COMMIT TRANSACTION
    
    
    

    More likely, though, is that there is a parameter-sniffing issue going. Test this in SSMS:

    SET ARITHABORT OFF
    EXEC ThisSP
    

    You will now run with the same SET options as the application (assuming that it goes with the default), and therefore use the same cache entry. So if now runs slow, you can start analysing query plans etc. For more details on SET ARITHABORT ON etc, there is an article on my web site: https://www.sommarskog.se/query-plan-mysteries.html.


0 additional answers

Sort by: Most helpful

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.