blocking behaviour using hints

Samantha r 1,326 Reputation points
2023-02-01T19:42:31.68+00:00
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 


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,292 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,071 Reputation points MVP
    2023-02-01T22:44:52.7733333+00:00

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 99,071 Reputation points MVP
    2023-02-04T10:30:20.6233333+00:00

    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.

    1 person found this answer helpful.

  2. CosmogHong-MSFT 20,371 Reputation points Microsoft Vendor
    2023-02-02T03:12:59.09+00:00

    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.