Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 154

This situation is one that the SQL Server platform handles automatically, regardless of the isolation
level. However, database developers can introduce this behavior themselves by performing an update
in two steps, rather than one. Consider this example (code i le Ch6LostUpdates.sql ):
/* SESSION 1*/
USE AdventureWorks2012;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 5;
BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;
WAITFOR DELAY '00:00:05.000';
UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;
SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;
Does it look OK? The developer has wrapped the read and the write in an explicit transaction, but all
this scenario needs is for some concurrent activity and a lost update will occur. The WAITFOR is only
present to make it easier to detonate the code. In a separate session, have the following code ready:
/* SESSION 2*/
USE AdventureWorks2012;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 100;
BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;
UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;
SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;