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

Now run Session 1; and then as soon as you have executed it, click over to
Session 2 and execute that code. Session 2 should come back almost immedi-
ately showing that the transaction has raised the safety stock level from 1,000
to 1,100 (see Figure 6-1). If you return to Session 1, you should now be able to
see that this transaction has also completed, except that the Safety Stock Level
has gone from 1,000 to 1,005 (see Figure 6-2). The design of the transaction is
l awed, causing an update to be lost.

FIGURE 6-1

What caused this loss? The developer wrote the transaction in such a way that
both sessions are able to read the data and store the stock level in a variable.
Consequently, when the update is made, both transactions start with the same value. This is a situ-
ation that should be avoided through more careful coding. Even raising the isolation level does not
resolve this particular problem, which should be addressed by performing the addition as part of the
update operation, as shown here:

FIGURE 6-2

UPDATE Production.Product
SET SafetyStockLevel += @Uplift
WHERE ProductID = 1;

We know you are all too smart to code your transactions in a way that could allow lost updates,
but it does show what can happen when insufi cient consideration is given to the transaction
design. Interestingly, SQL Server enables the syntax to support this behavior using the NOLOCK hint,
although it is largely ignored.

Dirty Reads
A dirty read takes no notice of any lock taken by another process. The read is ofi cially “dirty”
when it reads data that is uncommitted. This can become problematic if the uncommitted
transaction fails or for some other reason is rolled back.

Imagine a scenario in which you are shopping on a website and place an item into your basket and
proceed to payment. The site's checkout process decrements the stock by one and starts to charge
your card all in the one transaction. At that time, a second unrelated process starts. The website's
back ofi ce stock interface runs and makes a dirty read of all the product inventory levels, reading
the reduced value. Unfortunately, there is a problem with your transaction (insufi cient funds), and
your purchase transaction is rolled back. The website stock level has now reverted to the original
level, but the stock interface has just reported a different value.

You can run the following example against the AdventureWorks2012 database. Session 1 starts an
explicit transaction to update all persons with a last name of “Jones” to have the same i rst name
of “James.” This transaction will be rolled back after i ve seconds, and a SELECT is run to show the
original values (code i le Ch6DirtyReads.sql ):

/* SESSION 1 */
USE AdventureWorks2012;

BEGIN TRANSACTION;

UPDATE Person.Person