Recently I’ve read this post on how to handle hot rows in the upcoming version 8.0.1 of MySQL: http://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/. Also, while writing this article, I’ve come across this question on SQLServerCentral: https://www.sqlservercentral.com/Forums/1874362/Concurrency-data-access-with-UPDLOCK-and-READPAST. Given that these situations can definitely be considered real life scenarios for most highly concurrent applications, I decided to do some testing and documenting using Microsoft SQL Server.

First, let’s create a table for the seat booking scenario and put some rows on it:

-- create table
CREATE TABLE seats
(   seat_no INT PRIMARY KEY,
    booked  BIT
        DEFAULT 0
);
-- generate 100 sample rows
DECLARE @i INT = 0;
WHILE @i < 100
BEGIN
    SET @i = @i + 1;
    INSERT INTO seats (seat_no) VALUES (@i);
END;

Now let’s run a transaction using the updlock table hint, which means I want a select for update:

BEGIN TRANSACTION;
SELECT *
FROM seats WITH (UPDLOCK)
WHERE
    seat_no
    BETWEEN 2 AND 3
    AND booked = 0;
--release the locks
--commit

Check the locks:

You have an update intent page lock, as well as two-row locks. Run a new query with another transaction for different records on the same table:

BEGIN TRANSACTION;
SELECT *
FROM seats WITH (UPDLOCK)
WHERE
    seat_no
    BETWEEN 1 AND 7
    AND booked = 0;
--release the locks
--commit

Both result sets appeared instantly, the locks will be as follows:

Run the commit statements to release the locks. Execute both statements, but this time include all records on the second query statement:

BEGIN TRANSACTION;
SELECT *
FROM seats WITH (UPDLOCK)
WHERE
    seat_no
    BETWEEN 1 AND 7
    AND booked = 0;

Find blocked processes and command text:

SELECT
    DBName = db.name,
    tl.request_session_id,
    wt.blocking_session_id,
    BlockedObjectName = OBJECT_NAME(p.object_id),
    tl.resource_type,
    RequestingText = h1.text,
    BlockingTest = h2.text,
    tl.request_mode
FROM
    sys.dm_tran_locks tl
    INNER JOIN sys.databases db
        ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks wt
        ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions p
        ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1
        ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2
        ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) h2;
GO

You will observe that the second statement is being blocked by the locks on the first transaction:

For the sake of argument, let’s create the same table with a different name and set the ALLOW_PAGE_LOCKS option to OFF. Maybe if we get just some row level locking, the query will return some rows:

CREATE TABLE dbo.seats2
(   seat_no INT NOT NULL,
    booked  BIT NULL,
    PRIMARY KEY CLUSTERED (seat_no 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
ALTER TABLE dbo.seats2 ADD DEFAULT ((0)) FOR booked;
GO

Let’s, again, issue two separate transactions for the same set of rows. Run the first one and watch the difference on the lock types:

Great! The page lock is gone so I should be able to get some records? Not really, the second transaction is still blocked by the first because it needs rows currently held for the first transaction. And it’s the intended behavior because it should not let me alter records where I currently have pending transactions. If I had paid attention to the blocking process query, the lock type on the result is KEY, so I knew in advance that getting rid of page locks wouldn’t help.

For a hot rows scenario, where I really want to get the free rows and ignore the ones with pending transactions, I’ll have to resort to another t-sql table hint, namely READPAST.

Run the commit statements to release the locks. Execute the first statement again. Then execute the second statement, but this time use the READPAST hint:

BEGIN TRANSACTION;
SELECT *
FROM seats2 WITH (UPDLOCK, READPAST)
WHERE
    seat_no
    BETWEEN 1 AND 7
    AND booked = 0;

And there you go, the rows are returned instantly, leaving out the one locked on the first still pending transaction:

Using table hints you can override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table