The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2
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:
- "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.
- 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.
- 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.
- 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.
|