Isolation Level 2, repeatable read

Level 2 prevents nonrepeatable reads. These occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield results that are different from the original read. Isolation level 2 is supported only on data-only-locked tables. In a session at isolation level 2, isolation level 3 is also enforced on any tables that use the allpages locking scheme. Table 10-9 shows a nonrepeatable read in a transaction at isolation level 1.

**Table 10-9: Nonrepeatable reads in transactions**
T7	Event Sequence	T8
begin transaction select balance from account where acct_number = 25 select balance from account where acct_number = 25 commit transaction	T7 and T8 start. T7 queries the balance for one account. T8 updates the balance for that same account. T8 ends. T7 makes same query as before and gets different results. T7 ends.	begin transaction update account set balance = balance - 100 where acct_number = 25 commit transaction

Table 10-9: Nonrepeatable reads in transactions

Event Sequence

begin transaction

select balance
from account
where acct_number = 25







select balance
from account
where acct_number = 25

commit transaction

T7 and T8 start. T7 queries the balance for one account. T8 updates the balance for that same account. T8 ends. T7 makes same query as before and gets different results.

T7 ends.

begin transaction





update account
set balance = balance - 100
where acct_number = 25

commit transaction

If transaction T8 modifies and commits the changes to the account table after the first query in T7, but before the second one, the same two queries in T7 would produce different results. Isolation level 2 blocks transaction T8 from executing. It would also block a transaction that attempted to delete the selected row.