INF: Collisions During Multiple-User Updates

• Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0

SUMMARY

This article discusses several techniques for dealing with collisions when multiple users attempt to update the same data.

MORE INFORMATION

In an interactive application, there are four methods of handling collisions with other users:

1. "Last One Wins" is best in cases where the new value does not depend on the old one, such as updating a customer's address or phone number.



2. If the new value depends on the old one in an algorithmic way, such as updating an inventory or balance, use an atomic update instead of a separate read, compute new value, and update sequence (i.e., update inventory set count=count-1 instead of select count from inventory, count=count-1, update inventory).
   
   Use a trigger or additional WHERE conditions to guard against invalid conditions such as a negative inventory or balance.



3. In browse-type applications, timestamps can be used to detect that the data has changed since it was last read. The user can then be notified of the change (perhaps even shown the new values) and allowed to try again.



4. Method 3 is satisfactory in most cases, but if collisions are frequent or if a considerable amount of work must be redone (such as in an airline reservation system), it is better to "reserve" the item before investing a lot of work that may have to be redone. BROWSE provides no way to do this, and HOLDLOCK is not a solution because two users can get holdlocks and will later deadlock when they attempt to update.
   
   A set of rows can be "reserved" for update by doing a BEGIN TRANSACTION and then issuing an UPDATE that specifies the set, but changes nothing. This workaround performs an unnecessary update and prevents other users from reading the reserved rows, but it also prevents other users from updating the rows. This works best on a set of data which is retrieved with a UNIQUE CLUSTERED index.
   
   To handle the case of reserving a "slot," receive an item being inserted, insert a row with all default or null values, then update them when the actual values are supplied by the user.

Additional query words: Transact SQL

Keywords : kbother SSrvServer
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type :