How to block users updating records I have selected/read

We know that, with the default behavior, records are getting exclusively locked when we perform modifications against data, disallowing other users to access them. Until we complete the transaction, exclusive locks will be held by SQL Server, making sure that no one not only modify but execute SELECT statements too. However default settings does not hold locks obtained for SELECT statements blocking other users reading data you have read. But what if you need it?

If you want to make sure that no one modify records which you have read for your operation, you need to change the default Isolation Level and select data within a Transaction. The Isolation Level determines the behavior of concurrent users who read or write. Default Isolation Level is READ COMMITTED which locks records using shared locks for reading but does not hold them until the transaction is completed. In order to keep the obtained shared locks until the transaction is completed, Isolation Level has to be brought up to REPEATABLE READ Isolation Level.

REPEATABLE READ keeps acquired shared locks for read data till the end of transaction. This makes sure that no one can modify records we have read and avoid a concurrency issue called Inconsistency Analysis.

Here is the way of doing it.

Open a new connection and execute the following;

-- First connection
-- setting the isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- start a transaction
BEGIN TRAN

 -- executing a select query
 SELECT * 
 FROM Sales.SalesOrderHeader
 WHERE YEAR(OrderDate) = 2011;

Then try to modify a record related to above query with another connection.

-- Second connection

-- Try to retirve data
-- This works
SELECT * 
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;

-- Try to update a record
-- This will NOT work
UPDATE Sales.SalesOrderHeader
 SET ShipDate = DATEADD(dd, 1, shipdate)
WHERE SalesOrderID = 43660;

As you see, second connection will not be able to modify records until the transaction related to first connection is done.