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.
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.
