I’m writing this blog post in response to a question posed on twitter, asking which type of lock is preferable. The short answer is that at a high level it depends on your workload:
- OLTP
- OLAP
In writing this my assumption is that your performance goal is to achieve maximum concurrency. Whenever possible my preference is to provide a test in order to illustrate the point I’m trying to get across. First of all I will look at the OLTP scenario by creating six different tables each of which contains 100 rows:
CREATE TABLE [dbo].[NoPadding_RowLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_NoPadding_RowLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[NoPadding_RowLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[NoPadding_PageLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_NoPadding_PageLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = OFF
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[NoPadding_PageLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[OneRowPerPage_RowLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](4025) NOT NULL DEFAULT ('X')
,[padding_2] [char](4024) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_OneRowPerPage_RowLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[OneRowPerPage_RowLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[OneRowPerPage_PageLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](4025) NOT NULL DEFAULT ('X')
,[padding_2] [char](4024) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_OneRowPerPage_PageLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = OFF
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[OneRowPerPage_PageLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[TwoRowsPerPage_RowLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](4026) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_TwoRowsPerPage_RowLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TwoRowsPerPage_RowLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[TwoRowsPerPage_PageLock](
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](4026) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_TwoRowsPerPage_PageLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = OFF
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TwoRowsPerPage_PageLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[TwentyFiveRowsPerPage_PageLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](256) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_TwentyFiveRowsPerPage_PageLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = OFF
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TwentyFiveRowsPerPage_PageLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[TwentyFiveRowsPerPage_RowLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](256) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_TwentyFiveRowsPerPage_RowLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TwentyFiveRowsPerPage_RowLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[FiftyRowsPerPage_PageLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](128) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_FiftyRowsPerPage_PageLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = OFF
,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[FiftyRowsPerPage_PageLock] DEFAULT VALUES
GO 100
CREATE TABLE [dbo].[FiftyRowsPerPage_RowLock] (
[Id] [int] IDENTITY(1,1) NOT NULL
,[padding_1] [char](128) NOT NULL DEFAULT ('X')
CONSTRAINT [PK_FiftyRowsPerPage_RowLock] PRIMARY KEY
CLUSTERED (
[Id] ASC
) WITH ( PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[FiftyRowsPerPage_RowLock] DEFAULT VALUES
GO 100
To verify that the padding have achieved the desired result, sp_spaceused can be used to ascertain the the numbers of rows per page:
My theory is that the greater the ration of rows per page, the better the concurrency is when row locking is used instead of page locking. To test this I will require some stored procedures:
CREATE PROCEDURE [dbo].[uspNoPadding_PageLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM NoPadding_PageLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
CREATE PROCEDURE [dbo].[uspNoPadding_RowLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM NoPadding_RowLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
CREATE PROCEDURE [dbo].[uspOneRowPerPage_PageLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM OneRowPerPage_PageLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
CREATE PROCEDURE [dbo].[uspOneRowPerPage_RowLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM OneRowPerPage_RowLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
CREATE PROCEDURE [dbo].[uspTwoRowsPerPage_PageLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM TwoRowsPerPage_PageLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
CREATE PROCEDURE [dbo].[uspTwoRowsPerPage_RowLock] AS
BEGIN
DECLARE @i int = 0
,@j int;
WHILE @i < 1000000
BEGIN
SELECT @j = 1
FROM TwoRowsPerPage_RowLock
WHERE Id = @@SPID % 100;
SET @i += 1;
END;
END;
GO
For the purposes of brevity I have not included the code for the stored procedures that select from the tables with 25 and 50 rows per page. The isolation level used will be read committed and no trace flags will be used either. Each stored procedure will be fire off using ostress (which comes with rml ulities), the basic command line used for this is:
ostress -E -ddatabasename -Sserver –Q”sql statement” -nthreads
For the SQL statement a call to exec one of the stored procedures created earlier is used and 40 threads because 20 physical CPU cores and hyper threading enabled results in 40 logical processors being presented to SQL Server.
And now for the most interesting bit, . . . the actual test results:
Conclusion
For index seek heavy workloads in which only a handful of rows and being selected or inserted/deleted/merged/updated per query, as the number of rows per page increases, the more beneficial it becomes to use row locking instead of coarser grained page locks. I strongly suspect that PAGELATCH_SH waits are responsible for the increase in elapsed time for the tests using row level locking as the row density per page increases, which is something I will blog about another time.
What About OLAP Workloads
There are certain OLAP scenarios particularly with bulk loading where coarser grained locks result in better performance, this will be covered in a future blog post.
chrisadkin.io 
One thought on “Concurrency and Lock Granularity for OLTP Workloads”