Microsoft® SQL Server® 2012 Bible

1088

Part VIII: Performance Tuning and Optimization

Note two minor differences from the way application locks are handled by SQL Server:

■ Deadlocks are not automatically detected.

■ (^) If a transaction gets a lock several times, it must release that lock the same number
of times.

Application Locking Design

Aside from SQL Server locks, another locking issue deserves to be addressed. How the client

application deals with multiuser contention is important to both the user's experience and

the integrity of the data.

Implementing Optimistic Locking

The two basic means of dealing with multi-user access are optimistic locking and pessimis-

tic locking. The one you use determines the coding methods of the application.

Optimistic locking assumes that no other process will attempt to modify data while it is

currently being modifi ed. The idea is that you should read data, and then any time in the

future, update the data based on what you originally read. The disadvantage of optimistic

locking is that its multiple users can read and write the data because they aren’t blocked

from doing so by locks, but this can result in lost updates.

Pessimistic locking takes a different approach: With pessimistic locking, it is assumed

that processes contend to modify the same resources at the same time. When a process

modifi es data, a pessimistic locking scheme locks that data until the user has fi nished

with it.

Lost Updates

A lost update occurs when two users edit the same row, complete their edits, and save the

data, and the second user’s update overwrites the fi rst user’s update. For example:

1. Tim opens ProductID 876 from the Production.Product table, a 4 Bike Hitch
   Rack, in the front-end application. SQL Server applies a shared lock while retriev-
   ing the data.


2. Tim’s wife, Brittany, also opens ProductID 876 using the front-end application.


3. Tim and Brittany both make edits to the box-kite data. Tim rephrases the product
   description, and Brittany fi xes the product category.


4. Tim saves the data in the application, which sends an update to SQL Server. The
   update command replaces the old product description with Tim’s new description.

c47.indd 1088c47.indd 1088 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM

http://www.it-ebooks.info