When a select query includes multiple where clauses linked with and, Adaptive Server can apply the qualification for any columns that have not been affected by an uncommitted update of a row. If the row does not qualify because of one of the clauses on an unmodified column, the row does not need to be returned, so the query does not block.
If the row qualifies when the conditions on the unmodified columns have been checked, and the conditions described in the next section, Qualifying old and new values for uncommitted updates does not allow the query to proceed, then the query blocks until the lock is released.
For example, transaction T15 in Table 10-16 updates balance, while transaction T16 includes balance in the result set and in a search clause. However, T15 does not update the branch column, so T16 can apply that search argument.
Since the branch value in the row affected by T15 is not 77, the row does not qualify, and the row is skipped, as shown. If T15 updated a row where branch equals 77, a select query would block until T15 either commits or rolls back.
T15 |
Event Sequence |
T16 |
|---|---|---|
begin transaction update accounts set balance = 80 where acct_number = 20 and branch = 23 commit transaction |
T15 and T16 start. T15 updates accounts and holds an exclusive row lock. T16 queries accounts, but does not block because the branch qualification can be applied. |
begin transaction select acct_number, balance from accounts where balance < 50 and branch = 77 commit tran |
For select queries to avoid blocking when they reference columns in addition to columns that are being updated, 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 configuration parameter read committed with lock must be set to 0, the default value.
