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.