Microsoft SQL Server 2012 Bible

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 pessimistic 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 modified. 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 modifies data, a pessimistic locking scheme locks that data until the user has finished 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 first 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 retrieving the data.

2. Tim's wife, ...