Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Managing Concurrency

SQL Server provides two additional methods for handling multiuser programming situations:

• Optimistic locking, using time stamp tokens

• Application resource locking

Optimistic Locking

So far we have discussed active locks that block other users. Optimistic locks aren't really locks at all. Instead, when a user reads a row from a table, he also reads a rowversion column. The rowversion is a version stamp: It uniquely identifies a version of the row. Every time a row is updated, the server applies a new rowversion value to the column. If a user tries to update the row after the rowversion changes, the server will refuse the update.

Here is a simple example. The first query creates, populates, and retrieves the rows from a new