🔗 Understanding consistency in databases: beyond basic ACID

What to do when dealing with N multiple transactions at the same time?

Most of the time we perform CRUD operations under the pragmatic standard transactional scope, thinking it will suffice under all circumstances. After all, what could go wrong when we always apply a standard ACID tool provided by our frameworks, right? But it feels “good enough” only until we face corner cases of the real world production traffic: missing rows after pagination, duplicated updates, or metrics that refuse to add up.

Going deeper in the reasons of why to use more than the traditional transactional, scope can be hard without some real world examples of when or how should we use something different from what we use to. In this article I revisit those issues through a tiny Spring Boot/Java code base. The example we will use run through the following steps:

1. Paginate 5 000 Sale rows whose status starts as NOT_INITIALIZED.
2. Publisheach id to a Kafka topic.
3. Consumeeach message, updating its status to PROCESSING.
4. Demonstrate how plain idempotency checks and pagination fail under load.
5. Apply REPEATABLE READ isolation level and optimistic locking to restore consistency with much better performance.