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

of the transaction appears in the transaction log i les, rather than the data i les. The data i les are
updated later. For the time being, the change exists in memory (where processes can access the
updated data) and in the transaction log. Changes to the data i les happen shortly afterward, when
a separate CHECKPOINT operation takes place. Until then, the MDF i les do not contain the current
version of the database — for that, the MDF and LDF i les are both needed.
Therefore, the durability of a transaction is provided by the existence and preservation of the
database's transaction log. Database administrators protect their transaction logs above anything
else; because in the event of a failure, the transaction log is the only record of the latest database
changes.
For a minimally logged operation, the behavior is slightly different, and the transaction log contains
only sufi cient information to be able to commit or rollback the transaction fully; but the transac-
tion log still performs a vital role in ensuring that transactions are durable.
THE DANGERS OF CONCURRENCY
Before tackling the subject of locks, it is important to understand concurrency. Database concurrency
ensures that when multiple operations are occurring at once, the i nal result is still in agreement —
that they concur. This agreement typically depends on a set of rules and constraints that coordinate
the behaviors of transactions, making sure that different operations will play nicely together.
Having considered the attributes of your transactions that you need to protect, the following
sections consider the types of things that can happen if you let transactions have a free-for-all
environment — one where all the different transactions don't regard each other's boundaries, where
isolation is completely ignored. Later, you'll look at the various isolation levels in more detail, but in
this section if you look closely you'll often see the isolation level is set in the scripts.
The problems described next only occur when multiple sessions are occurring at once in your
database. This is typical behavior, I'm sure, but it's worth noting that in a single-session environ-
ment, these problems won't happen.
Lost Updates
A lost update occurs when two processes read the same data and then try to update the data with
a different value. Consider a scenario in which you and your partner have the romantic notion of
a joint bank account. On pay day, your respective employers both deposit your salaries into the
joint account. To perform the update, each process reads the data. At the time of the payments, all
is well in the world and you have an outstanding balance of $10,000. Each process therefore reads
$10,000 as its starting point. Your employer attempts to update the $10,000 i gure with your
monthly salary of $2,000, but at the same time your partner's employer updates the sum with his
or her salary of $4,000. Your partner's salary is added just before yours, updating the $10,000
balance to $14,000. Your payment then runs and updates the $10,000 balance to $12,000. A look
at the ATM shows $12,000. The i rst update has been lost, and even worse, it represented the big-
ger update!