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;