Database Reference
In-Depth Information
The problem occurs because transaction 2 is allowed to modify the data while transaction 1 has finished reading
the data and is about to make a decision on it. Transaction 1 requires a higher degree of isolation than that provided
by the default isolation level (Read Committed).
As a solution, you want to prevent transaction 2 from modifying the data while transaction 1 is working on it.
In other words, provide transaction 1 with the ability to read the data again later in the transaction without being
modified by others. This feature is called repeatable read. Considering the context, the implementation of the solution
is probably obvious. After re-creating the sample table, you can write this:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
GO
--Transaction 1 from Connection 1
DECLARE @Price INT ;
BEGIN TRAN NormalizePrice
SELECT @Price = 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
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --Back to default
GO
Increasing the isolation level of transaction 1 to Repeatable Read will prevent transaction 2 from modifying the
data during the execution of transaction 1. Consequently, you won't have an inconsistency in the price of the product.
Since the intention isn't to release the (S) lock acquired by the SELECT statement until the end of the transaction, the
effect of setting the isolation level to Repeatable Read can also be implemented at the query level using the lock hint.
DECLARE @Price INT ;
BEGIN TRAN NormalizePrice
SELECT @Price = Price
FROM dbo.MyProduct AS mp WITH (REPEATABLEREAD)
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
This solution prevents the data inconsistency of MyProduct.Price , but it introduces another problem to this
scenario. On observing the result of transaction 2, you realize that it could cause a deadlock. Therefore, although the
preceding solution prevented the data inconsistency, it is not a complete solution. Looking closely at the effect of the
Repeatable Read isolation level on the transactions, you see that it introduced the typical deadlock issue avoided by
the internal implementation of an UPDATE statement, as explained previously. The SELECT statement acquired and
Search WWH ::




Custom Search