RowLock Table Hint in SQL Server

Rowlock hint is used to specify that row – level locking should be used when accessing a table. This hint is useful in scenarios where we want to minimize locking contention only on the specific rows being accessed. It ensures that only the specific row being accessed are locked.

It is useful in high concurrency environment where multiple transactions are accessing the same table but different row. It is also used in OLTP system, when we are having many transactions access and modify small amount of data.

This hint specifies that a lock is taken on the row and held until the end of the statement or transaction.

Consider a situation where an e-commerce platform processes orders, and each order involves updating the order status in the database. To prevent interference from other transactions and ensure data consistency, the ROWLOCK hint is utilized:

Let’s see the example

 We have a table and inserting the records in this table.

create table orders_details ( order_id bigint identity(1,1) primary key, userid int  null, Productid int null, qnt int null, price decimal(18,10) null, order_status varchar(20) default 'Pending', order_dt datetime2 default current_timestamp, update_dt datetime2 default current_timestamp )  insert into orders_details(userid,Productid,qnt,price) values (1,1,5,15); insert into orders_details(userid,Productid,qnt,price) values (1,2,1,100); insert into orders_details(userid,Productid,qnt,price) values (1,3,2,50); insert into orders_details(userid,Productid,qnt,price) values (1,4,8,5);  insert into orders_details(userid,Productid,qnt,price,order_status) values (2,1,5,15,'Dispactch'); insert into orders_details(userid,Productid,qnt,price,order_status) values (3,2,1,100,'Cancle'); insert into orders_details(userid,Productid,qnt,price,order_status) values (4,3,2,50,'Processing'); insert into orders_details(userid,Productid,qnt,price,order_status) values (5,4,8,5,'in transit');

Now doing the update.

BEGIN TRANSACTION; -- Start the transaction and acquire row-level locks for the selected rows SELECT * FROM orders_details WITH (ROWLOCK) WHERE order_status = 'Pending'; -- Update the status of selected orders UPDATE orders_details SET order_status = 'Processing', update_dt=current_timestamp WHERE order_status = 'Pending';   COMMIT TRANSACTION; PRINT 'Order processing completed successfully.';

By incorporating the ROWLOCK hint, exclusive locks are acquired at the row level for the selected orders with a ‘Pending’ status. This ensures that only the rows being updated have exclusive locks, allowing other transactions to access and modify other rows concurrently, thereby improving concurrency and reducing contention. However, it’s essential to use ROWLOCK judiciously, considering its impact on concurrency and ensuring that the duration of the locks is minimized to avoid blocking other transactions unnecessarily.