Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 315

To prevent phantom reads, you need to move up in the isolation levels to SERIALIZABLE. For the 

most part, the SERIALIZABLE isolation level behaves similarly to REPEATABLE READ: namely, it requires 

a reader to obtain a shared lock to be able to read, and keeps the lock until the end of the transaction. 

But the SERIALIZABLE isolation level adds another facet—logically, this isolation level causes a reader 

to lock the whole range of keys that qualify for the query’s filter. This means that the reader locks not 

only the existing rows that qualify for the query’s filter, but also future ones. Or, more accurately, it 

blocks attempts made by other transactions to add rows that qualify for the reader’s query filter.

The following example demonstrates that the SERIALIZABLE isolation level prevents phantom 

reads. Run the following code in Connection 1 to set the transaction isolation level to SERIALIZABLE, 

open a transaction, and query all products with category 1.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

 

BEGIN TRAN 

 

  SELECT productid, productname, categoryid, unitprice 

  FROM Production.Products 

  WHERE categoryid = 1;

You get the following output, showing 12 products in category 1.

productid     productname      categoryid    unitprice 

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

1             Product HHYDP    1             18.00 

2             Product RECZE    1             19.00 

24            Product QOGNU    1             4.50 

34            Product SWNJY    1             14.00 

35            Product NEVTJ    1             18.00 

38            Product QDOMO    1             263.50 

39            Product LSOFL    1             18.00 

43            Product ZZZHR    1             46.00 

67            Product XLXQF    1             14.00 

70            Product TOONT    1             15.00 

75            Product BWRLG    1             7.75 

76            Product JYGFE    1             18.00 

 

(12 row(s) affected)

From Connection 2, run the following code in an attempt to insert a new product with category 1.

INSERT INTO Production.Products 

    (productname, supplierid, categoryid, 

     unitprice, discontinued) 

  VALUES('Product ABCDE', 1, 1, 20.00, 0);

In all isolation levels that are lower than SERIALIZABLE, such an attempt would have been success-

ful. In the SERIALIZABLE isolation level, the attempt is blocked. 

www.it-ebooks.info

---

316  

Microsoft SQL Server 2012 T-SQL Fundamentals

Back in Connection 1, run the following code to query products with category 1 a second time and 

commit the transaction.

  SELECT productid, productname, categoryid, unitprice 

  FROM Production.Products 

  WHERE categoryid = 1; 

 

COMMIT TRAN;

You get the same output as before, with no phantoms. Now that the reader’s transaction is com-

mitted, and the shared key-range lock is released, the modifier in Connection 2 can obtain the exclu-

sive lock it was waiting for and insert the row.

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

DELETE FROM Production.Products 

WHERE productid > 77;

Run the following code in all open connections to set the isolation level back to the default.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;