Isolation level 0, read uncommitted

Level 0, also known as read uncommitted, allows a task to read uncommitted changes to data in the database. This is also known as a dirty read, since the task can display results that are later rolled back. Table 1-7 shows a select query performing a dirty read.

Table 1-7: Dirty reads in transactions

T3

Event sequence

T4

begin transaction

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







rollback transaction

T3 and T4 start. T3 updates balance for one account by subtracting $100. T4 queries current sum of balance for accounts. T4 ends. T3 rolls back, invalidating the results from T4.

begin transaction





select sum(balance)
from account
where acct_number < 50

commit transaction

If transaction T4 queries the table after T3 updates it, but before it rolls back the change, the amount calculated by T4 is off by $100.The update statement in T3 acquires an exclusive lock on account. However, T4 does not try to acquire a shared lock before querying account, so it is not blocked by T3. The opposite is also true. If T4 begins to query account at isolation level 0 before T3 starts, T3 can still acquire its exclusive lock on account while T4’s query executes, because T4 does not hold any locks on the pages it reads.

At isolation level 0, Adaptive Server performs dirty reads by:

Any data modifications that are performed by T4 while the isolation level is set to 0 acquire exclusive locks at the row, page, or table level, and block if the data they need to change is locked.

If the table uses allpages locking, a unique index is required to perform an isolation level 0 read, unless the database is read-only. The index is required to restart the scan if an update by another process changes the query’s result set by modifying the current row or page. Forcing the query to use a table scan or a nonunique index can lead to problems if there is significant update activity on the underlying table, and is not recommended.

Applications that can use dirty reads may see better concurrency and fewer deadlocks than when the same data is accessed at a higher isolation level. If transaction T4 requires only an estimate of the current sum of account balances, which probably changes frequently in a very active table, T4 should query the table using isolation level 0. Other applications that require data consistency, such as queries of deposits and withdrawals to specific accounts in the table, should avoid using isolation level 0.

Isolation level 0 can improve performance for applications by reducing lock contention, but can impose performance costs in two ways:

During deferred update of a data row, there can be a significant time interval between the delete of the index row and the insert of the new index row. During this interval, there is no index row corresponding to the data row. If a process scans the index during this interval at isolation level 0, it does not return the old or new value of the data row. See “Deferred updates” in Chapter 1, “Understanding Query Processing” in Performance and Tuning Series: Query Processing and Abstract Plans.

sp_sysmon reports on these factors. See “Data Cache Management” in Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.