Achieving Database Consistency with⁣ Repeatable read and Optimistic Locking

‍ ⁣ Updated June‍ 02, 2025
‍

Standard create, read, update and delete ‍(CRUD) operations within a typical transactional scope often seem adequate.⁣ Though, real-world ⁤production scenarios can expose corner cases,⁣ such as missing rows in paginated data, duplicated ‍updates, and inconsistent metrics. Addressing these issues requires a deeper understanding of transactional scopes and option ⁤approaches.

To illustrate these challenges, consider a Spring ⁤Boot/Java request performing the following steps:

1. Retrieving 5,000 Sale rows with a NOT_INITIALIZED status using pagination.
2. Publishing each sale ID to a Kafka topic.
3. Consuming each⁢ message and updating the corresponding sale status to PROCESSING.
4. Demonstrating the failure of plain⁢ idempotency checks and pagination⁢ under heavy load.
5. applying REPEATABLE READ isolation and optimistic⁣ locking‍ to improve consistency and performance.

The degree of visibility that CRUD operations have when multiple transactions run ⁤concurrently defines the isolation level. In the sales example, this translates⁣ to how much a transaction⁢ updating the status from NOT_INITIALIZED to PROCESSING can affect, and be affected by, other concurrent transactions.

SQL databases typically support four isolation levels:

• READ UNCOMMITTED: Offers no guarantees and is rarely used. Transactions ⁢are not self-reliant, leading to “dirty reads” where uncommitted operations are visible to other transactions.
• READ COMMITTED: The ⁢default level. It ⁢prevents dirty reads by ensuring that only committed changes are visible. However, it allows “non-repeatable reads,” where the same query yields different results within a single transaction.
• REPEATABLE ⁤READ: Provides a⁢ stable snapshot of data for all operations within a transaction,preventing dirty and non-repeatable reads. However, it can fail in “write-skew” scenarios where business rules depend on observing updates from other transactions.
• SERIALIZABLE: Executes each CRUD operation sequentially, preventing all cited anomalies thru locking or abort-and-retry⁤ mechanisms.

While ⁤ SERIALIZABLE offers the highest level of isolation, it can significantly impact application performance under heavy workloads. ‍Therefore, locking strategies become essential.

Two classic locking approaches exist:

• Pessimistic Locking: Implemented using FOR UPDATE (row-level) or LOCK TABLE (table-level) ⁣in queries. it prevents other transactions from altering affected rows until locks are released. This approach performs well when ⁢write workloads are significantly higher than read workloads.
• Optimistic Locking: ⁤ Adds a version number or timestamp column‍ to the⁤ table. When a transaction⁣ modifies a row, it checks if the current version ⁢matches the database version using a condition like UPDATE ... WHERE id = ? AND version = ?. If the versions don’t match, a retry is performed.This ⁤approach is more efficient ⁣when read workloads are higher than write workloads.

What’s next

Understanding⁣ and applying appropriate isolation levels and locking strategies are ⁤crucial for maintaining data consistency in applications dealing with concurrent transactions. By carefully considering the trade-offs⁢ between isolation, performance, and workload characteristics,⁢ developers can build robust and reliable systems.