INF: Locking Multiple Tables in SQL Server

  - Microsoft SQL Server version 4.2 for OS/2

The following is the classical "repeatable read" problem:

Suppose a transaction must update multiple rows in multiple tables. Further, suppose that those updates are dependent on each other. Other users must be prevented from updating rows that are part of the set of rows being updated by the first transaction. Normal SQL Server concurrency control will prevent other users from updating rows that have already been updated by the first transaction, but not those rows it has not gotten to yet.

This problem is handled in SQL Server by HOLDLOCK. As long as other processes are prevented from changing what the read process has already read, there is no inconsistency. Any changes made by other processes "in front of" the reading process are equivalent to those changes having been made before the reading process ever started.

Although automatic locking is initially more difficult to understand than manual locking, one advantage is that "clean-up" is automatic and can be completely ignored by the application.