Database Reference
In-Depth Information
retained an (S) lock instead of a (U) lock, even though it intended to modify the data later within the transaction. The
(S) lock allowed transaction 2 to acquire a (U) lock, but it blocked the (U) lock's conversion to an (X) lock. The attempt
of transaction 1 to acquire a (U) lock on the data at a later stage caused a circular block, resulting in a deadlock.
To prevent the deadlock and still avoid data corruption, you can use an equivalent strategy as adopted by the
internal implementation of the
UPDATE
statement. Thus, instead of requesting an (S) lock, transaction 1 can request a
(U) lock by using an
UPDLOCK
locking hint when executing the
SELECT
statement.
DECLARE @Price INT ;
BEGIN TRAN NormalizePrice
SELECT @Price = Price
FROM dbo.MyProduct AS mp WITH (UPDLOCK)
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 both data inconsistency and the possibility of the deadlock. If the increase of the isolation
level to Repeatable Read had not introduced the typical deadlock, then it would have done the job. Since there is
a chance of a deadlock occurring because of the retention of an (S) lock until the end of a transaction, it is usually
preferable to grab a (U) lock instead of holding the (S) lock, as just illustrated.
Serializable
Serializable is the highest of the six isolation levels. Instead of acquiring a lock only on the row to be accessed, the
Serializable isolation level acquires a range lock on the row and the next row in the order of the data set requested.
For instance, a
SELECT
statement executed at the Serializable isolation level acquires a (RangeS-S) lock on the row
to be accessed and the next row in the order. This prevents the addition of rows by other transactions in the data set
operated on by the first transaction, and it protects the first transaction from finding new rows in its data set within its
transaction scope. Finding new rows in a data set within a transaction is also called a
phantom read.
To understand the need for a Serializable isolation level, let's consider an example. Suppose a group (with
GroupID = 10
) in a company has a fund of $100 to be distributed among the employees in the group as a bonus. The
fund balance after the bonus payment should be $0. Consider the following test table:
IF (SELECT OBJECT_ID('dbo.MyEmployees')
) IS NOT NULL
DROP TABLE dbo.MyEmployees ;
GO
CREATE TABLE dbo.MyEmployees
(EmployeeID INT,
GroupID INT,
Salary MONEY
) ;
CREATE CLUSTERED INDEX i1 ON dbo.MyEmployees (GroupID) ;
--Employee 1 in group 10
INSERT INTO dbo.MyEmployees
VALUES (1,10,1000),
