If a select query includes conditions on a column affected by an uncommitted update, and the query uses an index on the updated column, the query can examine both the old and new values for the column:
If neither the old or new value meets the search criteria, the row can be skipped, and the query does not block.
If either the old or new value, or both of them qualify, the query blocks In Table 10-17, if the original balance is $80, and the new balance is $90, the row can be skipped, as shown. If either of the values is less than $50, T18 must wait until T17 completes.
T17 |
Event Sequence |
T18 |
|---|---|---|
begin transaction update accounts set balance = balance + 10 where acct_number = 20 commit transaction |
T17 and T18 start. T17 updates accounts and holds an exclusive row lock; the original balance was 80, so the new balance is 90. T18 queries accounts using an index that includes balance. It does not block since balance does not qualify |
begin transaction select acct_number, balance from accounts where balance < 50 commit tran |
For select queries to avoid blocking when old and new values of uncommitted updates do not qualify, all of the following conditions must be met:
The table must use datarows or datapages locking.
At least one of the search clauses of the select query must be on a column that among the first 32 columns of the table.
The select query must run at isolation level 1 or 2.
The index used for the select query must include the updated column.
The configuration parameter read committed with lock must be set to 0, the default value.
