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

--Employee 2 in group 10
(2,10,1000),
--Employees 3 & 4 in different groups
(3,20,1000),
(4,9,1000);
The preceding business functionality may be implemented as follows:
DECLARE @Fund MONEY = 100,
@Bonus MONEY,
@NumberOfEmployees INT;
BEGIN TRAN PayBonus
SELECT @NumberOfEmployees = COUNT(*)
FROM dbo.MyEmployees
WHERE GroupID = 10;
/*Allow transaction 2 to execute*/
WAITFOR DELAY '00:00:10';
IF @NumberOfEmployees > 0
BEGIN
SET @Bonus = @Fund / @NumberOfEmployees;
UPDATE dbo.MyEmployees
SET Salary = Salary + @Bonus
WHERE GroupID = 10;
PRINT 'Fund balance =
' + CAST((@Fund - (@@ROWCOUNT * @Bonus)) AS VARCHAR(6)) + ' $';
END
COMMIT
You'll see the returned value as a fund balance of $0 since the updates complete successfully. The PayBonus
transaction works well in a single-user environment. However, in a multiuser environment, there is a problem.
Consider another transaction that adds a new employee to GroupID = 10 as follows and is executed concurrently
(immediately after the start of the PayBonus transaction) from a second connection:
BEGIN TRAN NewEmployee
INSERT INTO MyEmployees
VALUES (5, 10, 1000);
COMMIT
The fund balance after the PayBonus transaction will be -$50 ! Although the new employee may like it, the group
fund will be in the red. This causes an inconsistency in the logical state of the data.
To prevent this data inconsistency, the addition of the new employee to the group (or data set) under operation
should be blocked. Of the five isolation levels discussed, only Snapshot isolation can provide a similar functionality,
since the transaction has to be protected not only on the existing data but also from the entry of new data in the
data set. The Serializable isolation level can provide this kind of isolation by acquiring a range lock on the affected
row and the next row in the order determined by the MyEmployees.il index on the GroupID column. Thus, the data
inconsistency of the PayBonus transaction can be prevented by setting the transaction isolation level to Serializable.