blocking behaviour using hints

Question

Hi All, 

We are seeing SELECT's blocking DML's like UPDATE statements. 
When we tried to check with application team, they said they are using with (ROWLOCK) hint in most cases. 
So, I tried to repro but not able to reproduce the issue of SELECT with (ROWLOCK) hint blocking UPDATE statements.
Are there any cases where SELECT with rowlock can cause blocking? does number of rows plays any role like lock escalation or so?
Can anyone guide me on how to troubleshoot the issue? like any use case where ROWLOCK hint on SELECT stmt can cause blocking?  what data to be collected?


The database has default readcommitted isolation level and we are using SQL Server 2017 EE.

demo 
=====

CREATE TABLE [dbo].[EMP](
	[EMPNO] [int] NOT NULL,
	[ENAME] [varchar](10) NULL,
	[JOB] [varchar](9) NULL,
	[MGR] [int] NULL,
	[HIREDATE] [datetime] NULL,
	[SAL] [numeric](7, 2) NULL,
	[COMM] [numeric](7, 2) NULL,
	[DEPTNO] [int] NULL,
 CONSTRAINT [PK_EMP] PRIMARY KEY CLUSTERED 
(
	[EMPNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7369, N'SMITH', N'CLERK', 7902, CAST(N'1980-12-17T00:00:00.000' AS DateTime), CAST(800.00 AS Numeric(7, 2)), NULL, 20)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7499, N'ALLEN', N'SALESMAN', 7698, CAST(N'1981-02-20T00:00:00.000' AS DateTime), CAST(1600.00 AS Numeric(7, 2)), CAST(300.00 AS Numeric(7, 2)), 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7521, N'WARD', N'SALESMAN', 7698, CAST(N'1981-02-22T00:00:00.000' AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(500.00 AS Numeric(7, 2)), 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7566, N'JONES', N'MANAGER', 7839, CAST(N'1981-04-02T00:00:00.000' AS DateTime), CAST(2975.00 AS Numeric(7, 2)), NULL, 20)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7654, N'MARTIN', N'SALESMAN', 7698, CAST(N'1981-09-28T00:00:00.000' AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(1400.00 AS Numeric(7, 2)), 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7698, N'BLAKE', N'MANAGER', 7839, CAST(N'1981-05-01T00:00:00.000' AS DateTime), CAST(2850.00 AS Numeric(7, 2)), NULL, 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7782, N'CLARK', N'MANAGER', 7839, CAST(N'1981-06-09T00:00:00.000' AS DateTime), CAST(2450.00 AS Numeric(7, 2)), NULL, 10)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7788, N'SCOTT', N'ANALYST', 7566, CAST(N'1987-06-12T00:00:00.000' AS DateTime), CAST(3000.00 AS Numeric(7, 2)), NULL, 20)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7839, N'KING', N'PRESIDENT', NULL, CAST(N'1981-02-12T00:00:00.000' AS DateTime), CAST(5000.00 AS Numeric(7, 2)), NULL, 10)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7844, N'TURNER', N'SALESMAN', 7698, CAST(N'1981-09-08T00:00:00.000' AS DateTime), CAST(1500.00 AS Numeric(7, 2)), CAST(0.00 AS Numeric(7, 2)), 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7876, N'ADAMS', N'CLERK', 7788, CAST(N'1987-01-22T00:00:00.000' AS DateTime), CAST(1100.00 AS Numeric(7, 2)), NULL, 20)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7900, N'JAMES', N'CLERK', 7698, CAST(N'1981-12-06T00:00:00.000' AS DateTime), CAST(950.00 AS Numeric(7, 2)), NULL, 30)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7902, N'FORD', N'ANALYST', 7566, CAST(N'1981-12-08T00:00:00.000' AS DateTime), CAST(3000.00 AS Numeric(7, 2)), NULL, 20)
GO
INSERT [dbo].[EMP] ([EMPNO], [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DEPTNO]) VALUES (7934, N'MILLER', N'CLERK', 7782, CAST(N'1982-01-12T00:00:00.000' AS DateTime), CAST(1300.00 AS Numeric(7, 2)), NULL, 10)
GO





spid = 55
=============


BEGIN TRAN 
SELECT * FROM [dbo].[emp] WITH (ROWLOCK)
---SELECT * FROM [dbo].[emp] WITH (XLOCK)
--WHERE ename IN ('SMITH','ALLEN') 
--commit;



spid = 56
===============
use Scott
go
UPDATE emp SET sal=sal+10000;



spid = 57
=========
sp_WhoIsActive
 @Output_Column_List = '[dd hh:mm:ss.mss][session_id][block%][status][database_name][open_tran_count][locks][sql_text],
 [additional%][host_name][program_name][login%]'
 , @Find_Block_Leaders = 1
 ,@show_sleeping_spids= 1
 , @Get_Locks = 1
 , @Get_Additional_Info=1

Thanks,
Sam

Accepted Answer

SELECT statements can block UPDATE statements, yes. The ROWLOCK hint forces locks to be taken by row, but there can still be escalation to table lock, I guess. But even if locks are by row, an UPDATE that is wants to update a row that a SELECT statement locks will be blocked.

To understand why the blocking is occurring, you need to get more information. One way is to activate the blocked process report. First step is

EXEC sp_configure 'blocked process threshold (s)', 5
RECONFIGURE

This will cause SQL Server to generate reports for blocking that has lasted 5 seconds. These reports can be consumed by Trace, Extended Events or Event Notifications. They will give you an XML document where you get information about the locks and the processes. It is very important that you check for open transactions, as blocking locks may have been taken earlier in the transaction!

You typically set the value in multiple of five seconds, since SQL Server checks for deadlocks and blocking with this frequency.

Answer

Yes, if you enable READ_COMMITTED_SNAPSHOT on, SELECT statements will not block update statements, as long as they are using the READ COMMITTED isolation level. If you are using REPEATABLE READ or SERIALIZABLE, they can still block.

The same is true if you enable ALLOW_SNAPSHOT_ISOLATION and then explicitly set the isolation level to SNAPSHOT. But note that if some queries use hints like UPDLOCK, HOLDLOCK etc, they are specifying a different isolation level which can result in blocking.

And in both cases, note that if a process does something like:

BEGIN TRANSACTION
UPDATE tbl ...
SELECT ... FROM -- runs for minutes

And then you have a blocking situation where you only look at the current statements, you may jump to conclusion that the SELECT is blocking the other process, when it is in fact the locks from the UPDATE statement which are the culprit. This is why I wanted you to collect the blocking process report, as that report will tell you that there is a transaction in progress.

Finally, while any form of snapshot is great for reducing locking, you need to be aware of that you are reading stale data. That is, if data is in the progress of being updated, you get the old and no longer current data. In many cases, this is acceptable, but not always.

Answer

Hi @Samantha r

ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks aren't taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

See Table hints (Transact-SQL).

One option would be to enable Read Committed Snapshot Isolation in that database:

ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;

This should stop those UPDATE queries from blocking your read queries.

Note that this will affect the results of the read queries, since they will be seeing a point-in-time snapshot of the data being read, rather than being blocked waiting for the update. It will also increase load on tempdb. Please refer to this article for more details: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

blocking behaviour using hints

2 additional answers