Select queries that do not reference the updated column

A select query on a datarows-locked table can return values without blocking, even though a row is exclusively locked when:

Transaction T14 in Table 10-15 requests information about a row that is locked by T13. However, since T14 does not include the updated column in the result set or as a search argument, T14 does not block on T13’s exclusive row lock.

Table 10-15: Pseudo-column-level locking with mutually-exclusive columns


Event Sequence


begin transaction

update accounts 
set balance = 50
where acct_number = 35

commit transaction

T13 and T14 start. T13 updates accounts and holds an exclusive row lock. T14 queries the same row in accounts, but does not access the updated column. T14 does not block.

begin transaction

select lname, fname, phone
from accounts 
where acct_number = 35
commit transaction

If T14 uses an index that includes the updated column (for example, acct_number, balance), the query blocks trying to read the index row.

For select queries to avoid blocking when they do not reference updated columns, all of the following conditions must be met: