1089
Chapter 47: Managing Transactions, Locking, and Blocking
47
- Brittany presses the Save and Close button, and her data is sent to SQL Server in
another update statement. The product category is now fi xed, but the old descrip-
tion was in Brittany’s form, so Tim’s new description was overwritten with the old
description. - Tim discovers the error and complains to the IT vice president during the next
round of golf about the unreliability of that new SQL Server–based database.
Minimizing Lost Updates
If the application uses an optimistic locking scheme, you can minimize the chance that a
lost update can occur, as well as minimize the effects of a lost update, using the following
methods:■ If the update statement is constructed by the front-end application, ensure it
updates only those columns actually changed by the user. This technique alone
would prevent the lost update in the previous example of Tim and Brittany’s
updates, and most lost updates in the real world. As an added benefi t, it reduces
client/server traffi c and the workload on SQL Server.■ (^) If an optimistic locking scheme is not an option, then the application uses a “he
who writes last, wins” scheme. This is otherwise known as pessimistic locking, and
was outlined in the section earlier on Isolation Levels. Although lost updates may
occur, a data-audit trail can minimize the effect by exposing updates to the same
row within minutes and tracking the data changes. This can allow you to identify
those scenarios and potentially put additional safeguards in place in your applica-
tion. One option for this would be to add application logic to superfi cially “lock”
a row. The application could keep track of what is and isn’t locked so that lost
updates do not occur. In my experience, this is often very unnecessary. The isola-
tion levels exposed in SQL Server are more than suffi cient at providing segregation
between data manipulation changes between transactions.
Transaction-Log Architecture
The ACID properties of a transaction are enforced in SQL Server through the Write-Ahead
Logging (WAL) protocol. The WAL protocol ensures the Durability of a transaction by
ensuring that all modifi cations are fi rst written to the transaction log before they are writ-
ten to the data fi le. Once a transaction has been hardened to the transaction log, it can be
re-created (or rolled back) in the event of a server crash.Transaction-Log Sequence
Every data-modifi cation operation goes through the same sequence, in which it writes fi rst
to the transaction log and then to the data fi lec47.indd 1089c47.indd 1089 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM
http://www.it-ebooks.info