REPEATABLE READ isolation level in SQL server

 REPEATABLE READ is an isolation level in SQL Server that guarantees that rows read during a transaction cannot be modified by other transactions until the current transaction completes. SQL Server achieves this by holding Shared Locks on the rows for the entire duration of the transaction rather than releasing them after each statement. This prevents Dirty Reads and Non-Repeatable Reads. However, REPEATABLE READ does not protect against Phantom Reads because it locks existing rows but not the range of values being searched. As a result, other transactions can still insert new rows that match the query criteria. Compared to READ COMMITTED, it provides stronger consistency but increases blocking and the possibility of deadlocks. SERIALIZABLE is the next level above REPEATABLE READ and additionally prevents Phantom Reads by using key-range locks.

See the demo

Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN; SELECT * FROM Employee WHERE EmployeeID = 1;  --commit;

Transaction is not yet committed.

Session 2

We are trying to update this row.

UPDATE Employee SET Salary = 70000 WHERE EmployeeID = 1;

Still this query is waiting to complete the session 1.

Again, we are running the select query of the session 1.

In this way we can prevent the dirty read and non-repeatable read. Once we commit the session 1 transaction then only session 2 update the record.

After commit session 2 update the record

Now row value is changed after transaction completed.

Phantom Read : REPEATABLE READ protects existing rows but not new rows.

Let’s see

Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN; SELECT count(*) FROM Employee;   --commit;

It returns 3 rows. Transaction is still opened.

Session 2:

Insert a new record in this table.

BEGIN TRAN; INSERT INTO Employee(EmployeeID,EmployeeName,Salary) values (4,'MAhesh',70000) commit

Record inserted successfully. Now running the select on session 1.

We are getting the count as 4. This is called phantom read. This isolation level not prevent the phantom read. It occurs because REPEATABLE READ locks only the rows that were read.