Concurrency and Lock Granularity for OLTP Workloads

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:

sp_spaceused
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:

elapsed time

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.

One thought on “Concurrency and Lock Granularity for OLTP Workloads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s