A select query on a datarows-locked table can return values without blocking, even though a row is exclusively locked when:
The query does not reference an updated column in the select list or any clauses (where, having, group by, order by or compute), and
The query does not use an index that includes the updated column
Transaction T14 in Table 2-16 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.
T13 |
Event Sequence |
T14 |
|---|---|---|
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:
The table must use datarows locking.
The columns referenced in the select query must be among the first 32 columns of the table.
The select query must run at isolation level 1.
The select query must not use an index that contains the updated column.
The configuration parameter read committed with lock must be set to 0, the default value.
