SQL Tip

I wanted to simulate range locks. I tried some examples in web, I couldn't actually repro it. Roji suggested below repro steps. We can see range locks and also why you see range locks in this scenario.

Created a Test Table and Populated Test Data as below
USE TestDatabase
IF OBJECT_ID ('TestLocks') IS NOT NULL DROP TABLE TestLocks
CREATE TABLE TestLocks
(Id INT Identity (1,1) PRIMARY KEY CLUSTERED ,
Value CHAR(20) )

DECLARE @I INT
SET @I = 0
   WHILE 1=1
BEGIN
   INSERT INTO TestLocks(Value)
   VALUES ('Value' + CONVERT(VARCHAR(20), @I))
   SET @I = @I+1
   IF @I > 100
   BREAK;
END

Now we have learnt in earlier post Repeatable Read can cause phantom read.
Read Committed Isolation Level - Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time

In case of a Repeatble read for below query
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM TestLocks WHERE ID> 50
SELECT * FROM sys.dm_tran_locks where resource_type <> 'Database'
ROLLBACK TRAN

Lock request mode is Shared - Shared locks are placed and are held until the transaction completes

Same transaction when ran under SERIALIZABLE Isolation level would have a Range Lock.

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT * FROM TestLocks WHERE ID> 50
SELECT * FROM sys.dm_tran_locks where resource_type <> 'Database'

SERIALIZABLE - highest level, where transactions are completely isolated from one another. From MSDN "Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction"....

Hope it helps...Happy Reading...