IMPLICIT_TRANSACTIONS

Question

IMPLICIT_TRANSACTIONS

vsamantha35

SSChampion

Points: 11551
More actions
October 18, 2023 at 5:16 am

#4310154

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.
Once 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?
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

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

wat's the isolation level the sproc is called with from the application ?

regarding implicite transactions, it all depends when the application will require its first lock and how much work it still needs to do after that point in time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Check the connection objects used by your application