Rows that can appear or disappear from a results set are called phantoms. Some queries that require phantom protection (queries at isolation level 3) use range locks.
Isolation level 3 requires serializable reads within the transaction. A query at isolation level 3 that performs two read operations with the same query clauses should return the same set of results each time. No other task can be allowed to:
Modify one of the result rows so that it no longer qualifies for the serializable read transaction, by updating or deleting the row
Modify a row that is not included in the serializable read result set so that the row now qualifies, or insert a row that would qualify for the result set
Adaptive Server uses range locks, infinity key locks, and next-key locks to protect against phantoms on data-only-locked tables. Allpages-locked tables protect against phantoms by holding locks on the index pages for the serializable read transaction.
When a query at isolation level 3 (serializable read) performs a range scan using an index, all the keys that satisfy the query clause are locked for the duration of the transaction. Also, the key that immediately follows the range is locked, to prevent new values from being added at the end of the range. If there is no next value in the table, an infinity key lock is used as the next key, to ensure that no rows are added after the last key in the table.
Range locks can be shared, update, or exclusive locks; depending on the locking scheme, they are either row locks or page locks. sp_lock output shows “Fam dur, Range” in the context column for range locks. For infinity key locks, sp_lock shows a lock on a nonexistent row, row 0 of the root index page and “Fam dur, Inf key” in the context column.
Every transaction that performs an insert or update to a data-only-locked table checks for range locks.
