Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 228

ConcurrencyEffects
As stated earlier, concurrency deals with the simultaneous access of a set of data. If the database engine
did not control concurrency, there could be several unwanted side effects. Those are lost updates, dirty
reads, non-repeatable reads, and phantom reads.
A lost update means that two transactions update the same piece of data and only the last transaction's
update of the data is captured.
Dirty reads refer to the notion that a piece of data is in the processing of being updated by a transaction.
However, before that update is committed, a second transaction reads the same piece of data. If this
transaction gets the uncommitted data, there's a possibility that the transaction updating the data will
change something, and the second transaction's copy of the data won't match what's been written.
Non-repeatable reads are similar to dirty reads with the following difference. In this case, the data read
by the second transaction does read committed data from the first transaction. The other factor here is
that the second transaction reads the same piece of data two or more times, and gets different results.
Thus, the second transaction is unable to repeat the read. Avoiding this is accomplished by not allowing
any other transactions to read the changing piece of data until the last of the modifications are saved and
the transaction is committed.
Finally, phantom reads happen when a row is inserted or deleted by one transaction into a range of rows
being read by a second transaction. When another read is executed against the same range of rows there
appears to be fewer or more rows than with the original read. Like non-repeatable reads this can be
avoided by restricting access to the piece of data from readers until all the writing is completed.
ConcurrencyControl
Controlling concurrency in order to address the issues above concerning the effects is the next order of
business. There are two types of concurrency control: pessimistic and optimistic.
Pessimistic control tends to ensure that read operations access current data. This means that a transaction
reading a piece of data will create a lock that prevents other transactions from modifying that data until
the read operation is completed.
Optimistic control tends toward minimizing the blocking that can occur among reader and writer trans-
actions of the same piece of data. This is accomplished by the fact that read operations don't acquire locks
that would prevent other transactions from modifying the piece of data.
IsolationLevels
Along with maximizing row density, the mechanism for implementing concurrency control is through
setting isolation levels. Isolation levels are designated on a transaction occurring against a database.
They are either pessimistic or optimistic. The idea here is that one transaction is being isolated from other
transactions in varying degrees. The isolation levels dictate which of the concurrency effects listed above
are allowed. Also, it's important to realize that setting an isolation level only controls read operations.
Additionally, that level essentially dictates what's allowed for data modification by other transactions.