February 1, 2023 at 7:43 pm
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
February 2, 2023 at 12:40 pm
Absolutely ROWLOCK is going to cause blocking. In fact, using the ROWLOCK hint may even cause more blocking. Hints are not hints. Hints are commandments that the optimizer and query engine will follow slavishly if they can. Forcing a ROWLOCK on a query where a lock on the page, extent or even table would be more efficient (a single lock on each of those entities, versus thousands or even millions of locks on rows) can seriously impact performance. I strongly recommend using locking hints as sparingly as possible. I would absolutely not recommend putting them everywhere.
With a locking hint in place, you won't see lock escalation, so that's not a concern here.
Troubleshooting this is pretty simple. There's a Blocked Process Report you can capture from SQL Server. Here's some good documentation on how all this works. There's a blocked process threshold you can set, and then you can capture an Extended Event to see processes that are blocked. It will include what is called the block chain, all the processes that are blocked, and which processes that are blocking them. Further, it'll show you the row, rows, page, pages, extent, extents or tables that are involved in the blocking.
In all likelihood, your ROWLOCK hints are forcing the query engine to take out, and hold, locks longer than it would if it locked on a page, pages, extent or extents. You have to remember, locks involve I/O, certainly, but also memory management and CPU (less so). This means large numbers of row locks (as opposed to a single page lock, for example) will need a lot more memory. That means the reads, the SELECT statement, will hold the locks longer while it waits to get access to the needed memory, making the blocking problem worse.
Get rid of the ROWLOCK hints. Probably 99%+ of your queries do not need them and are actively being hurt by them. Next, start testing READ COMMITTED SNAPSHOT isolation level. This addresses almost all issues arising between reads impacting writes and vice versa.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2023 at 4:41 pm
Many thanks Sir. Will try the blocked process report.
February 8, 2023 at 7:02 am
Grant is totally right.
I will add my voice to using snapshot isolation level, it fixed a shed load of blocking problems in my old place, particularly those from third-party applications. You turn it on at database level so you have a degree of control.
You need to look at temp dB size and speed as snapshot isolation will use more temp db space. Do not use snapshot isolation for ReportServer db as this prevents the page number processing from working.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 8, 2023 at 4:46 pm
Post deleted.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2023 at 8:28 pm
Ha! I may have overly played up the "scary" part of the scarydba occasionally.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2023 at 4:01 am
Ha! I may have overly played up the "scary" part of the scarydba occasionally.
I apologize. I didn't intend to embarrass, my dear ol' friend.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2023 at 1:15 pm
Grant Fritchey wrote:Ha! I may have overly played up the "scary" part of the scarydba occasionally.
I apologize. I didn't intend to embarrass, my dear ol' friend.
Whoops. You didn't have to delete that. It's publicly available on my blog. No worries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply