In DB2 isolation levels, what is the main difference between Cursor Stability and Repeatable Read?

Introduction / Context:
Isolation levels control how DB2 manages concurrency and visibility of data changes. Cursor Stability and Repeatable Read are two important isolation levels. This interview question tests whether the candidate understands how long read locks are held under each level and how that affects repeatable reads of data within a transaction.

Given Data / Assumptions:

• The program uses cursors to read rows from a DB2 table.
• Different isolation levels can be specified for the unit of work.
• We are comparing Cursor Stability and Repeatable Read.

Concept / Approach:
Under Cursor Stability, DB2 holds a read lock on the current row but releases it when the cursor moves to the next row. This allows other transactions to update rows that have been read already, which can lead to non repeatable reads. Under Repeatable Read, DB2 holds read locks on all rows referenced by the transaction until commit or rollback, ensuring that the same query returns consistent results. The correct answer must describe this difference in lock duration, not unrelated issues like dirty reads or performance claims.

Step-by-Step Solution:
Step 1: Recall that Cursor Stability is often the default isolation level in DB2. Step 2: Under Cursor Stability, once the cursor moves past a row, the lock on that row is typically released. Step 3: Under Repeatable Read, locks on all rows that have been read are held until the transaction completes. Step 4: This means that another transaction cannot modify those rows until the first transaction issues COMMIT or ROLLBACK. Step 5: Choose the option that clearly contrasts these behaviors in terms of lock release timing.

Verification / Alternative check:
Examples in DB2 documentation often show that under Cursor Stability, rereading a set of rows later in the same transaction may yield different values if another transaction has updated them in the meantime. Under Repeatable Read, this cannot happen because the original transaction continues to hold locks on the rows it read. This behavior directly supports the explanation in option A.

Why Other Options Are Wrong:
Option B is wrong because dirty reads, which involve reading uncommitted data, are associated with lower isolation like Uncommitted Read, not with Cursor Stability.
Option C is wrong because Repeatable Read typically uses more locks and can reduce concurrency compared to Cursor Stability; it is not guaranteed to be faster.
Option D is wrong because both isolation levels can be used in batch or online programs as needed; they are not tied to specific environments.

Common Pitfalls:
A common pitfall is to equate higher isolation levels with better performance, when in fact they often reduce concurrency and may increase lock contention. Another mistake is to assume that Cursor Stability prevents all anomalies; it still allows non repeatable reads and phantom rows. Developers and DBAs should choose isolation levels that balance consistency requirements with performance goals.

Final Answer:
The main difference is that Cursor Stability releases read locks as the cursor moves past rows, while Repeatable Read holds read locks on all referenced rows until the end of the transaction.