Isolation levels
From PTAGISWiki
Contents |
Isolation Levels
Summary:
Isolation levels allow a compromise between consistency and concurrency making it possible to increase concurrency when the accuracy of the data is not essential. The isolation levels are: Read Uncommitted, Read Committed, Repeatable Read and Serializable
Problem:
Resolution:
Isolation levels allow users to specify an appropriate compromise between consistency and concurrency. This feature makes it possible to increase concurrency when the absolute consistency and accuracy of the data is not essential.
Ingres supports four isolation levels defined by the ANSI/ISO SQL92 standard
- Read Uncommitted (RU)
- Read Committed (RC)
- Repeatable Read (RR)
- Serializable
The highest degree of isolation is called "serializable", since the concurrent execution of serializable transactions is equivalent to a serial execution of the transactions. Serializable execution is the default behavior of Ingres transactions, since it offers the highest degree of protection to the application programmer. This highest degree of isolation, however, is the lowest degree of concurrency. At lower degrees of isolation, more transactions may run concurrently, but it can also introduce some inconsistencies. The ANSI/ISO specifies three inconsistencies that can occur during the execution of concurrent transaction:
Dirty Read
Transaction T1 modifies a row. Transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.
Non-Repeatable Read
Transaction T1 reads a row. Transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
Phantom Rows
Transaction T1 reads the set of rows N that satisfy some . Transaction T2 then executes SQL statements that generate one or more rows that satisfy the <search condition> used by transaction T1. If transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.
Table below shows how the ANSI standard defines which inconsistencies are possible (Yes) and not possible (No) for a given isolation level.
| Dirty Read | Non-repeatable Read | Phantom Rows | |
|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | Yes |
| SERIALIZABLE | No | No | No |
For programmers who are aware of possible inconsistencies, lower degrees of isolation can dramatically improve throughput. The most commonly-cited example of this is a cursor-based program which wishes to scan through a large table, examining many rows but updating only a few rows. Under normal serializable execution, this transaction will take share locks on all rows (or pages) which it reads (typically, in practice, it will take a shared lock on the entire table), thus locking out all update activity on the table until the transaction commits or aborts, which may be a long time.
The RU isolation level provides greatly increased read and write concurrency, but it suffers from the "dirty read" anomaly. The greater concurrency is achieved because the RU transaction does not acquire locks on data being read and other transactions can immediately read or/and modify the same rows. RU is ideal for applications where the reading of uncommitted data is not a major concern.
The RC isolation level is well suited to allow increased concurrency in a more controlled fashion than at the RU level. RC transactions do not perform dirty reads but rather holds a lock locks on data while reading the data. For 'cursoed' queries a lock is held on the current data item (page or row) pointed by the cursor. The lock is automatically released when the cursor is positioned to the next item or closed. However, if any data on the current item of the cursor is changed, the lock must be held until the transaction commits. Such locking strategy is known as CURSOR STABILITY, and it defines an isolation level slightly stronger than 'classical' RC.
The reason for the cursor stability at the RC isolation level is to prevent cursor lost updates that are possible if locks are released immediately after data are read. The problem occurs when a transaction T1 running at the 'classical' RC isolation level reads a data item and then transaction T2 updates the data item and commits, then T1 updates the data based on its earlier read value and also commits. The T2's update is lost! Because of the cursor stability, this problem this problem does not exist in Ingres at the RC and higher isolation levels. At the same time, the RC mode does not guarantee that a transaction sees the same data if it repeats the initial read.
Cursor stability assumes that whenever the user is accessing a row with a cursor, this row is locked. However, if the user issues a complex cursor declaration which involves a join, and the results of the join are placed into a temporary buffer to be sorted before being updated, the assumption might be wrong. The problem exists because, in this case, the FETCH statement returns rows to the user not from the base table, but from the temporary buffer. When the user then attempts to update the "current" row of the cursor, the server will locate the proper row of the base table by its TID taken from the temporary buffer. The user would be expecting a lock to be held on the base table row until the row had been processed, but at the RC isolation level the lock will be released when the row is placed into the temporary buffer. Therefore, the row to update may no longer exist or no longer meet the criteria in the WHERE clause. To prevent this problem the server will automatically upgrade the isolation level from RC to RR.
In RR isolation mode locks are automatically released from data which were opened for reading, but never read. With this option, if the application process returns to the same page and reads the same row again, the data cannot have changed. At the same time, repeatable read does not prevent concurrent inserts: if we issue the same SELECT statement twice (within the same transaction), we can get "phantom rows".
Serializable mode requires that a selected set of data not change until transaction commit. The page locking protocols prevent phantoms, since the page LOCKS cover the pages that would hold the phantom. To prevent phantoms for a serializable row locking transaction, extra locks are required. These locks include data page locks for the isam and heap table, value locks for hash table and leaf page locks for B-tree table.
An isolation level is automatically increased from RC and RR to serializable for any operation on system catalogs and during the checking of integrity constraints or the execution of actions associated with referential constraints. This is necessary to insure data integrity. However, if an integrity constraint is implemented by a user-defined rule, it is the user's responsibility to provide the appropriate isolation level.
For additional information on Ingres Isolation Levels refer to the Ingres DBA Guide
