INF: Using Separate Connections for Update in Browse Mode

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

This article explains locking when using separate connections for an update in browse mode.

MORE INFORMATION

SQL Server considers different connections to be different users. This means that the select connection could block (or deadlock) the update connection. 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 connection cannot be used to update any rows read by the first connection.

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 connections will have no lock contention. Browse mode uses a timestamp column to control data integrity problems that might result from concurrent updates.