When a select query on an allpages-locked table performs a table scan at isolation level 1, it first acquires a shared intent lock on the table and then acquires a shared lock on the first data page. It locks the next data page, and drops the lock on the first page, so that the locks “walk through” the result set. As soon as the query completes, the lock on the last data page is released, and then the table-level lock is released. Similarly, during index scans on an allpages-locked table, overlapping locks are held as the scan descends from the index root page to the data page. Locks are also held on the outer table of a join while matching rows from inner table are scanned.
select queries on data-only-locked tables first acquire a shared intent table lock. Locking behavior on the data pages and data rows is configurable with the parameter read committed with lock, as follows:
If read committed with lock is set to 0 (the default) then select queries read the column values with instant-duration page or row locks. The required column values or pointers for the row are read into memory, and the lock is released. Locks are not held on the outer tables of joins while rows from the inner tables are accessed. This reduces deadlocking and improves concurrency.
If a select query needs to read a row that is locked with an incompatible lock, the query still blocks on that row until the incompatible lock is released. Setting read committed with lock to 0 does not affect the isolation level; only committed rows are returned to the user.
If read committed with lock is set to 1, select queries acquire shared page locks on datapages-locked tables and shared row locks on datarows-locked tables. The lock on the first page or row is held, then the lock is acquired on the second page or row and the lock on the first page or row is dropped.
Cursors must be declared as read-only to avoid holding locks during scans when read committed with lock is set to 0. Any implicitly or explicitly up datable cursor on a data-only-locked table holds locks on the current page or row until the cursor moves off the row or page. When read committed with lock is set to 1, read-only cursors hold a shared page or row lock on the row at the cursor position.
read committed with lock does not affect locking behavior on allpages-locked tables. For information on setting the configuration parameter, see in the System Administration Guide.
