Isolation Level 1, read committed

Level 1, also known as read committed, prevents dirty reads. Queries at level 1 can read only committed changes to data. At isolation level 1, if a transaction needs to read a row that has been modified by an incomplete transaction in another session, the transaction waits until the first transaction completes (either commits or rolls back.)

For example, compare Table 10-8, showing a transaction executed at isolation level 1, to Table 10-7, showing a dirty read transaction.

Table 10-8: Transaction isolation level 1 prevents dirty reads

T5

Event Sequence

T6

begin transaction

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






rollback transaction

T5 and T6 start. T5 updates account after getting exclusive lock. T6 tries to get shared lock to query account but must wait until T5 releases its lock. T5 ends and releases its exclusive lock. T6 gets shared lock, queries account, and ends.

begin transaction





select sum(balance)
from account
where acct_number < 50





commit transaction

When the update statement in transaction T5 executes, Adaptive Server applies an exclusive lock (a row-level or page-level lock if acct_number is indexed; otherwise, a table-level lock) on account.

If T5 holds an exclusive table lock, T6 blocks trying to acquire its shared intent table lock. If T5 holds exclusive page or exclusive row locks, T6 can begin executing, but is blocked when it tries to acquire a shared lock on a page or row locked by T5. The query in T6 cannot execute (preventing the dirty read) until the exclusive lock is released, when T5 ends with the rollback.

While the query in T6 holds its shared lock, other processes that need shared locks can access the same data, and an update lock can also be granted (an update lock indicates the read operation that precedes the exclusive-lock write operation), but no exclusive locks are allowed until all shared locks have been released.