SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 80

The default in SQL Server is pessimistic concurrency, so a system of locking and un-locking resources
is used to enable applications to have high concurrency. However, with the release of SQL Server 2005,
you have the ability to adopt an optimistic strategy, which is possible because SQL Server is now able
to use row versioning . This allows a reader to read the data as it was before the start of a modification
from a writer. We will come back to row versioning later (and the isolation level that uses it) after
looking at what anomalies can occur with concurrency and the different isolation levels you can use to
prevent them.
LostUpdates
A lost update occurs when two processes read the same data and both try to update the original value.
Imagine if you share a bank account and you go into a branch to deposit $400 in cash while your partner
who works on the other side of town goes into a different branch to deposit $500 in cash. The bank teller
at each branch simultaneously reads the current balance as $1000. Your bank teller adds $400 to $1000 and
updates the balance to $1400 and your partner's bank teller adds $500 to the $1000 that they originally
read and updates the balance to $1500. You just lost $400 because of a lost update. Clearly, this is an
unacceptable side effect.
DirtyReads
A dirty read is a read of uncommitted data. One process modifies data and then cancels before
committing the new value, but another process has already read the changed value and started using
it. For example, you tell a real estate agent that you'd like to sell your house and she adds it to the list of
available properties. You change your mind before you complete the paperwork and it gets taken off the
list. However, before it was taken off, another agent found a buyer because their list included properties
without completed paperwork. The second agent read dirty or uncommitted data and took an action based
on the value that shouldn't have been possible.
Non-RepeatableReads
A non-repeatable read is when a process that reads data twice within a transaction might get
different results on the second read. For example, you add up all the money in ten cash registers
in your store one by one and then do it again to double-check your sums only to find the results
to be vastly different. As you finished adding up one register, it started taking money again, so when
you came back around to read it a second time, you got a different figure. Your initial reads were
non-repeatable.
Phantoms
Phantoms occur when a row is inserted or deleted by one transaction into a range being read by another
transaction. For example, you search a recruitment website for DBA positions in your area and get
12 results. You create 12 cover letters for your resume and search the website again to get the e-mail
addresses. You get 13 results back but you've only got 12 covering letters. The extra position you got that
you didn't get in the first search is a phantom row.
These anomalies aren't always un-wanted and which ones could occur depends on the isolation level of
your transaction .