Key-range locking solves the phantom problem and supports serializable transactions. Key-range locks cover individual records and the ranges between records, preventing phantom insertions or deletions into a set of records accessed by a transaction. Key-range locks are taken only on behalf of transactions operating at the serializable isolation level.
Serializability requires that any query executed during a transaction must obtain the same set of rows if it is executed again at some later point within the same transaction. If this query attempts to fetch a row that does not exist, the row must not be inserted by other transactions until the transaction that attempts to access the row completes. If a second transaction were allowed to insert the row, it would appear as a phantom.
If a second transaction attempts to insert a row that resides on a locked data page, page-level locking prevents the phantom row from being added, and serializability is maintained. However, if the row is added to a data page not already locked by the first transaction, then a locking mechanism should be in place to prevent the row from being added.
A key-range lock works by covering the index rows and the ranges between those index rows rather than locking the entire base table rows. Because any attempt to insert, update, or delete any row within the range by a second transaction requires a modification to the index, the second transaction is blocked until the first transaction completes, because key-range locks cover the index entries.
Key-range locks include both a range and a row component, specified in range-row format:
| Range | Row | Mode | Description |
|---|---|---|---|
| Null | S | Nul-S | Null range, shared lock on resource; shared lock on row. |
| IS | S | IS-S | Intent-shared range, shared resource lock; serializable range scan. |
| IIN | Null | IIN-Nul | Intent-insert range, null resource lock; used to test ranges prior to inserting a new key into an index. |
| IS | U | IS-U | Intent-shared range, update resource lock; serializable update scan. |
| IU | X | IU-X | Intent-update range, exclusive resource lock; used when updating a key in a range. |
Note The internal Null lock mode is compatible with all other lock modes.
Key-range locking ensures that these four scenarios are serializable:
The following examples for each of the four scenarios are based upon this table and index.
To ensure a range scan query is serializable, the same query should return the same results each time it is executed within the same transaction. New rows must not be inserted within the range scan query by other transactions; otherwise, these become phantom inserts. For example, given the following query using the table and index above:
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'
Key-range locks are placed on the index entries corresponding to the range of data rows where name falls between the values Adam and Dale, preventing new rows qualifying in the above query from being added or deleted. Although the first name in this range is Adam, the IS-S mode key-range lock on this index entry ensures that no new names beginning with the letter A can be added before Adam, such as Abigail. Similarly, the IS-S key-range lock on the index entry for Dale ensures that no new names beginning with the letter C can be added after Carlos, such as Clive.
Note The number of IS-S locks held is n+1, where n is the number of rows that satisfy the query.
If a query within a transaction tries to select a row that does not exist, then issuing the query at a later point within the same transaction has to return the same result. No other transaction can be allowed to insert that nonexistent row. For example, given this query:
SELECT name FROM mytable WHERE name = 'Bill'
A key-range lock is placed on the index entry corresponding to the name range Ben to Bing, because the name Bill would be inserted between these two adjacent index entries. The IS-S mode key-range lock is placed on the index entry Bing. This prevents any other transaction from inserting values, such as Bill, between the index entries Ben and Bing.
When deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation. Locking the deleted key value until the end of the transaction is sufficient to maintain serializability. For example, given this DELETE statement:
DELETE mytable WHERE name = 'Bob'
An exclusive (X) lock is placed on the index entry corresponding to the name Bob. Other transactions can insert or delete values before or after the deleted value Bob. However, any transaction attempting to read, insert, or delete the value Bob will block, until the deleting transaction either commits or rolls back.
When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. For example, given this INSERT statement:
INSERT mytable VALUES ('Dan')
The IIN-Nul mode key-range lock is placed on the index entry corresponding to the name David to test the range. If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value Dan. The IIN-Nul mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. Other transactions can insert or delete values before or after the inserted value Dan. However, any transaction attempting to read, insert, or delete the value Dan will block, until the inserting transaction either commits or rolls back.