MPLICIT_TRANSACTIONS

Question

MPLICIT_TRANSACTIONS

Sam 1,476

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

ZoeHui-MSFT 41,551 Reputation points

2023-10-20T06:59:10.4433333+00:00

Do you have further question on this, could we offer more support?

If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer accepted by question author

0 additional answers

Your answer

ZoeHui-MSFT 41,551 Reputation points

2023-10-20T06:59:10.4433333+00:00

Do you have further question on this, could we offer more support?

If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

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.

Sam 1,476 Reputation points

2023-10-19T08:13:09.8066667+00:00

okay. The isolation level is shown as "Read committed".

Just curious, actually, RCSI is also turned on the database. Why SQL Server is not showing it as "Read committed Snapshot Isolation" level?

Thanks,

Sam
Sam 1,476 Reputation points

2023-10-19T08:16:20.3+00:00

any other dmv's which it will show as RCSI?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-10-19T21:05:52.32+00:00

Why SQL Server is not showing it as "Read committed Snapshot Isolation" level?

Because the isolation level is READ COMMITTED. RCSI may feel like a different isolation level, but it is really just a different implementation. And once the database has been enabled for READ_COMMITTED_SNAPSHOT, you will get RSCI. You can override for a specific table in a specific query with the READCOMMITEDLOCK hint, but there is no SET TRANSACTION ISOLATION LEVEL READCOMMITTEDLOCK.

Share via

MPLICIT_TRANSACTIONS

0 additional answers

Your answer