Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 309

The query is still blocked because Connection 1 hasn’t yet ended the update transaction, but if 

after 5 seconds the lock request is not satisfied, SQL Server terminates the query and you get the fol-

lowing error.

Msg 1222, Level 16, State 51, Line 3 

Lock request time out period exceeded.

Note that lock timeouts do not roll back transactions.

To remove the lock timeout value, set it back to the default (indefinite), and issue the query again, 

run the following code in Connection 2.

SET LOCK_TIMEOUT -1; 

 

SELECT productid, unitprice 

FROM Production.Products -- WITH (READCOMMITTEDLOCK) 

WHERE productid = 2;

To terminate the update transaction in Connection 1, run the following code from Connection 3 

(assuming you’re connected to an on-premises SQL Server instance).

KILL 52;

This statement causes a rollback of the transaction in Connection 1, meaning that the price change 

of product 2 from 19.00 to 20.00 is undone, and the exclusive lock is released. Go to Connection 2. 

Notice that you get the data after the change is undone—namely, before the price change.

productid   unitprice 

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

2           19.00