Using alternative predicates to skip nonqualifying rows

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.

Table 10-16: Pseudo-column-level locking with multiple predicates


Event Sequence


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: