Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 408

transaction. If the outcome of the decision is dependent on the data read by the SELECT statement, then you should
consider preventing modification of the data by other concurrent transactions. For example, consider the following
two transactions:
• Normalize the price for ProductID = 1 : For ProductID = 1 , if Price > 10 , then decrease the
price by 10 .
• Apply a discount : For products with Price > 10 , apply a discount of 40 percent .

Now consider the following test table:

IF (SELECT OBJECT_ID('dbo.MyProduct')
) IS NOT NULL
DROP TABLE dbo.MyProduct ;
GO
CREATE TABLE dbo.MyProduct
(ProductID INT,
Price MONEY
) ;
INSERT INTO dbo.MyProduct
VALUES (1, 15.0) ;

You can write the two transactions like this:

DECLARE @Price INT ;
BEGIN TRAN NormailizePrice
SELECT @Price = mp.Price
FROM dbo.MyProduct AS mp
WHERE mp.ProductID = 1 ;
/*Allow transaction 2 to execute*/
WAITFOR DELAY '00:00:10' ;
IF @Price > 10
UPDATE dbo.MyProduct
SET Price = Price - 10
WHERE ProductID = 1 ;
COMMIT

--Transaction 2 from Connection 2
BEGIN TRAN ApplyDiscount
UPDATE dbo.MyProduct
SET Price = Price * 0.6 --Discount = 40%
WHERE Price > 10 ;
COMMIT

On the surface, the preceding transactions may look good, and yes, they do work in a single-user environment.
But in a multiuser environment, where multiple transactions can be executed concurrently, you have a problem here!
To figure out the problem, let's execute the two transactions from different connections in the following order:

1. Start transaction 1 first.

2. Start transaction 2 within ten seconds of the start of transaction 1.
As you may have guessed, at the end of the transactions, the new price of the product (with ProductID = 1 ) will
be -1.0 . Ouch—it appears that you're ready to go out of business!