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 2-7 shows a select query performing a dirty read.
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 transaction T3 acquires an exclusive lock on account. However, transaction 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 accounts at isolation level 0 before T3 starts, T3 could still acquire its exclusive lock on accounts 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:
Allowing another task to read rows, pages, or tables that have exclusive locks; that is, to read uncommitted changes to data.
Not applying shared locks on rows, pages or tables being searched.
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 non unique 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 reduced 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:
Dirty reads make in-cache copies of dirty data that the isolation level 0 application needs to read.
If a dirty read is active on a row, and the data changes so that the row is moved or deleted, the scan must be restarted, which may incur additional logical and physical I/O.
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 will not return the old or new value of the data row. See “Deferred updates” on page 95 in Performance and Tuning: Optimizer.
sp_sysmon reports on these factors. See “Dirty read behavior” on page 284 in Performance and Tuning: Monitoring and Analyzing.
