Sql server® 2012 t-sql fundamentals

The 

READ COMMITTED

 Isolation Level

If you want to prevent readers from reading uncommitted changes, you need to use a stronger isola-

tion level. The lowest isolation level that prevents dirty reads is READ COMMITTED, which is also the 

default isolation level in an on-premises SQL Server installation. As the name indicates, this isolation 

level allows readers to read only committed changes. It prevents uncommitted reads by requiring a 

reader to obtain a shared lock. This means that if a writer is holding an exclusive lock, the reader’s 

www.it-ebooks.info

---

312  

Microsoft SQL Server 2012 T-SQL Fundamentals

shared lock request will be in conflict with the writer, and it has to wait. As soon as the writer commits 

the transaction, the reader can get its shared lock, but what it reads are necessarily only committed 

changes.

The following example demonstrates that, in this isolation level, a reader can only read committed 

changes.

Run the following code in Connection 1 to open a transaction, update the price of product 2, and 

query the row to show the new price.

BEGIN TRAN; 

 

  UPDATE Production.Products 

    SET unitprice += 1.00 

  WHERE productid = 2; 

 

  SELECT productid, unitprice 

  FROM Production.Products 

  WHERE productid = 2;

This code returns the following output.

productid   unitprice 

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

2           20.00

Connection 1 now locks the row for product 2 exclusively. 

Run the following code in Connection 2 to set the session’s isolation level to READ COMMITTED 

and query the row for product 2 (remember to uncomment the hint in SQL Database to use READ 

 COMMITTED instead of READ COMMITTED SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 

 

SELECT productid, unitprice 

FROM Production.Products -- WITH (READCOMMITTEDLOCK) 

WHERE productid = 2;

Keep in mind that this isolation level is the default, so unless you previously changed the session’s 

isolation level, you don’t need to set it explicitly. The SELECT statement is currently blocked because 

it needs a shared lock to be able to read, and this shared lock request is in conflict with the exclusive 

lock held by the writer in Connection 1. 

Next, run the following code in Connection 1 to commit the transaction.

COMMIT TRAN;

Now go to Connection 2 and notice that you get the following output.

productid   unitprice 

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

2           20.00

www.it-ebooks.info

---