Sql server® 2012 t-sql fundamentals

The 

REPEATABLE READ

 Isolation Level

If you want to ensure that no one can change values in between reads that take place in the same 

transaction, you need to move up in the isolation levels to REPEATABLE READ. In this isolation level, 

not only does a reader need a shared lock to be able to read, but it also holds the lock until the 

end of the transaction. This means that as soon as the reader has acquired a shared lock on a data 

resource to read it, no one can obtain an exclusive lock to modify that resource until the reader ends 

the transaction. This way, you’re guaranteed to get repeatable reads, or consistent analysis.

The following example demonstrates getting repeatable reads. Run the following code in Connec-

tion 1 to set the session’s isolation level to REPEATABLE READ, open a transaction, and read the row 

for product 2.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 

 

BEGIN TRAN; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

This code returns the following output showing the current price of product 2.

productid   unitprice 

----------- --------------------- 

2           19.00

Connection 1 still holds a shared lock on the row for product 2 because in REPEATABLE READ, 

shared locks are held until the end of the transaction. Run the following code from Connection 2 to 

try to modify the row for product 2.

UPDATE Production.Products 

  SET unitprice += 1.00 

WHERE productid = 2;

www.it-ebooks.info

---

314  

Microsoft SQL Server 2012 T-SQL Fundamentals

Notice that the attempt is blocked because the modifier’s request for an exclusive lock is in conflict 

with the reader’s granted shared lock. If the reader was running under the READ UNCOMMITTED or 

READ COMMITTED isolation level, it wouldn’t have held the shared lock at this point, and the attempt 

to modify the row would have been successful.

Back in Connection 1, run the following code to read the row for product 2 a second time and 

commit the transaction.

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2; 

 

COMMIT TRAN;

This code returns the following output.

productid   unitprice 

----------- --------------------- 

2           19.00

Notice that the second read got the same unit price for product 2 as the first read. Now that the 

reader’s transaction has been committed and the shared lock is released, the modifier in Connection 2 

can obtain the exclusive lock it was waiting for and update the row.

Another phenomenon prevented by REPEATABLE READ but not by lower isolation levels is called 

a lost update. A lost update happens when two transactions read a value, make calculations based on 

what they read, and then update the value. Because in isolation levels lower than REPEATABLE READ 

no lock is held on the resource after the read, both transactions can update the value, and whichever 

transaction updates the value last “wins,” overwriting the other transaction’s update. In REPEATABLE 

READ, both sides keep their shared locks after the first read, so neither can acquire an exclusive lock 

later in order to update. The situation results in a deadlock, and the update conflict is prevented. I’ll 

provide more details on deadlocks later in this chapter, in the “Deadlocks” section.

When you’re done, run the following code for cleanup.

UPDATE Production.Products 

  SET unitprice = 19.00 

WHERE productid = 2;