Isolation Levels — part VIII: Cursor Stability

Cursor Stability is not defined in SQL Standard, and I’ve seen it only in DB2. The Cursor Stability isolation level prevents lost updates. A lost update occurs when a transaction reads a row for updating and another transaction has updated and committed its change between your read and write operations. The concurrent update is lost because your writes are based on the values that were read before and ignores the new value. An example is select value into var from sequence; var:=var+1; update sequence set amount=var;. Cursor Stability will ensure that the first select will wait if another transaction has an ongoing update. If you are in a lower level of isolation, you have two solutions to acheive the same:

• use pessimistic locking with a select for update
• use a single statement like update sequence set value=value+1 returning value-1

Cursor Stability is a typical isolation level in non-MVCC databases. In this scenario, the rows that are read are locked, but the lock can be released earlier than at the end of the transaction, reducing the probability of deadlocks. This isolation level offers improved concurrency compared to Repeatable Read by releasing the read lock sooner but doesn’t prevent read skew because another select can see the same rows with different values. In contrast, MVCC databases do not lock rows for reads and typically do not require an intermediate isolation level between Read Committed and Repeatable Read.

Originally published at https://dev.to on December 8, 2023.