Locking selected records until the transaction is completed

SQL Server handles locks efficiently and it places relevant locks based on the way we access records. If you perform an action such as INSERT, UPDATE, DELETE against records, SQL Server makes sure that records for the operation are exclusively locked and no one can access them. However, what if you need to make sure that no one can modify your selected records until you complete the transaction?

Generally, SQL Server obtains shared locks when we retrieve records using SELECT statement but it does not keep them. The obtained locks are immediately released once all records are sent to the client.

We can use REPEATABLE READ Isolation Level for handling this. This Isolation Level makes sure that records selected during the transaction cannot be modified by other users until we complete the transaction.

There is another easy way of handling this. We can use UPDLOCK Hint with the SELECT statement, locking the records exclusively. This hint takes Update Locks for Read Operation only at the row level or page level. See the underlined words, Page Level, means you might see that few number of records are locked even though you have selected only one record, because if SQL Server takes a page lock, then all records in the page will be locked.

However, for most cased, this helps to protect the record until we complete the process on selected records. Here is the way of doing it.

Execute this with a new Connection

USE AdventureWorks2014
GO

-- Starting a transaction
BEGIN TRAN

-- Selecting a record
SELECT * FROM Production.Product
WITH (UPDLOCK)
WHERE ProductID = 4

Now execute this with another connection and see.

USE AdventureWorks2014;
GO

-- This is possible, can get the record
SELECT * FROM Production.Product
WHERE ProductID = 4;

-- This is not possible until the 
-- first tranaction is completed
UPDATE Production.Product
 SET Color = 'Black'
WHERE ProductID = 4;

Reading records that are not locked - READPAST hint

When a set of records are being updated, with the default Isolation Level which is Read Committed, SQL Server makes sure that no one can read them until the transaction is completed. This avoids Dirty Read concurrency issue but it decreases concurrency.

What if you need to read rest of the records that are no locked? Now the question is, how can we instruct to SQL Server to skip locked records and read other records. It makes possible with READPAST hint.

The READPAST hint allows us to read records by ignoring locked record without waiting till incompatible locks to be released. This is NOT similar to READUNCOMMITTED hint because it reads all records including records that are locked.

Here is an example:

Execute the following query in a new window.

USE AdventureWorks2014;
GO

SELECT * FROM Production.Product;

-- This starts a transaction and locks set of records
BEGIN TRAN
 UPDATE Production.Product
  SET Color = 'Black'
 WHERE Color IS NULL;

-- ROLLBACK TRAN 

Now try following codes with another window

-- This wait until locks are released.
SELECT * FROM Production.Product

-- This returns all records including 
-- records that are being changed.
SELECT * FROM Production.Product WITH (READUNCOMMITTED);

-- This returns all records excluding
-- records that are being changed.
SELECT * FROM Production.Product WITH (READPAST);

See the difference in results. You may find READPAST hint useful with certain scenario.