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.