Setting different transaction isolation level on sqlserver probe in CA UIM

Document ID : KB000112620
Last Modified Date : 31/08/2018
Show Technical Document Details
Introduction:
In the sqlserver probe Troubleshooting page, section Database is Locked describes how to add key 'no_lock' with value '1' to connection configurations .
Question:
How the 'no_lock' flag helps to avoid to deadlocks in MS SQL instance.

 
Environment:
All recent versions of sqlserver probe.
Answer:
Result of setting no_lock=1 in connection definition inside sqlserver_monitor.cfg is that below statements are added before each queries: 

SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


when they are sent to database instance. 

This avoids using SHARED locks when sending queries to db instance as described in MS article: 

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Additional Information:
The no_lock key applies to all queries sent to database via this connection. If this is not required, create custom checkpoint and add 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' statement before the query or use NOLOCK hint in the query used in custom checkpoint.

If you want to set different transaction isolation level other than the default 'READ COMMITTED' and if you are aware of the consequence, you can set stricter transaction isolation level in custom checkpoint.