ID Number: Q45253
1.00 1.10 1.11 4.20
OS/2
Summary:
This article explains locking when using separate pipes for an update
in browse mode.
More Information:
SQL Server considers different pipes to be different users. This means
that the select pipe could block (or deadlock) the update pipe. The
select statement acquires a shared page lock on each page that it
reads. If HOLDLOCK is not used, it releases each shared page lock as
soon as it finishes reading the page. Whenever there are unprocessed
rows from a select statement, the page containing the first
unprocessed row remains locked until all rows from that page are
processed. Then, the lock is moved to the next page. Unfortunately,
the page containing the first unprocessed row probably will also
contain the row that is to be updated.
If HOLDLOCK is used, the shared locks are held until commit, and a
second pipe cannot be used to update any rows read by the first pipe.
Note that browse mode creates a temporary table of result data so that
there are no locks left on the underlying table. As a result, the two
pipes will have no lock contention. Browse mode uses a timestamp
column to control data integrity problems that might result from
concurrent updates.
Additional reference words: 1.00 1.10 1.11 4.20