Isolation Levels
Isolation levels allow users a balance 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 (R)
• Repeatable Read (RR)
• Serializable
The highest degree of isolation is Serializable and is the default behavior of Ingres transactions because it offers the highest degree of consistency but the lowest degree of concurrency.
At lower degrees of isolation (such as Read Uncommitted), more transactions can run concurrently, but inconsistencies can occur.
An isolation level is set by using the statements:
SET SESSION ISOLATION LEVEL
SET TRANSACTION ISOLATION LEVEL
Inconsistencies During Concurrent Transactions
The ANSI/ISO specifies three inconsistencies that can occur during the execution of concurrent transactions:
• Dirty Read
– Transaction T1 modifies a row.
– Transaction T2 reads that row before T1 performs a commit.
– If T1 performs a rollback, T2 reads a row that was never committed and is considered to have never existed.
• Non-repeatable Read
– Transaction T1 reads a row.
– Transaction T2 modifies or deletes that row and performs a commit.
– If T1 attempts to reread the row, it can 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 search condition.
– Transaction T2 executes SQL statements that generate one or more rows that satisfy the search condition used by transaction T1.
– If transaction T1 repeats the initial read with the same search condition, it obtains a different collection of rows.
Inconsistencies and Isolation Levels
The following table shows how the ANSI standard defines which inconsistencies are possible (Yes) and impossible (No) for a given isolation level:
For programmers who are aware of possible inconsistencies, lower degrees of isolation can improve throughput.
Read Uncommitted Isolation Level
The Read Uncommitted (RU) isolation level provides greatly increased read and write concurrency, but “dirty reads” may occur. RU is ideal for applications where the reading of uncommitted data is not a concern. Greater concurrency is achieved because the RU transaction does not acquire locks on data being read and other transactions can immediately read or modify the same rows.
Read Committed Isolation Level
The Read Committed (RC) isolation level allows increased concurrency that is more controlled than at the RU level. RC transactions do not perform dirty reads but rather hold a lock on data while reading the data. For "cursored" queries, a lock is held on the cursor’s current page or row.
• The lock is automatically released when the cursor is positioned to the next item or the cursor is closed.
• If the current row is changed, the lock is held until the transaction commits.
This locking strategy is called cursor stability. Cursor stability at RC isolation level prevents cursor lost updates that could occur if locks are released immediately after data is read.
For example:
• Transaction T1 running with RC isolation level reads a data item.
• Transaction T2 updates the data item and commits.
• T1 updates the data based on its earlier read value and commits.
• T2’s update is lost.
Because of cursor stability, this does not occur in Ingres at RC and higher isolation levels.
Note: RC does not guarantee that the 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, that the row is locked.
However, if the cursor is complex (involves a join or a sort), the results are placed into a temporary buffer. The FETCH statement returns rows from the temporary buffer, not from the base table. When the current row is updated, the row is located in the base table by TID held in the temporary buffer.
The user expects a lock to be held on the base table row until the row has been processed, but at the RC isolation level, the lock is released when the row is placed into the temporary buffer. Therefore, the row to be updated may no longer exist or may have changed. To prevent this, the DBMS automatically changes the isolation level for the query from Read Committed to Repeatable Read.
Repeatable Read Isolation Level
In Repeatable Read (RR) isolation mode, locks are automatically released from data opened for reading but never read. With RR, if the application returns to the same page and re-reads the row, the data cannot have changed. If a SELECT statement is issued twice in the same transaction, “phantom rows” can occur. RR does not prevent concurrent inserts.
Serializable Isolation Level
The Serializable isolation mode requires that a selected set of data not change until transaction commit. The page locking protocols prevent phantoms, readers block writers.
Row-level locking can provide repeatable read, but this requires extra locks. These locks include data page locks for the ISAM and heap tables, value locks for the hash table, and leaf page locks for the B-tree table.
An isolation level is automatically increased from RC and RR to serializable for:
• Any operation on system catalogs
• During the checking of integrity constraints
• The execution of actions associated with referential constraints
This increase is necessary to ensure 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.