According to the section ' Database is Locked' in the sqlserver probe Troubleshooting page, adding the key 'no_lock' with value '1' to connection configurations helps avoiding the locking issues after probe connecting to SQL Server instances. How can the 'no_lock' flag help to avoid deadlocks in MS SQL instance?
- All recent versions of sqlserver probe
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 running queries in MS SQL instances as described in MS article:
SET TRANSACTION ISOLATION LEVEL (Transact-SQL
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' , please be aware of any consequences of applying a looser or stricter transaction isolation level in custom checkpoint.