How to ensure data consistency in a distributed system

Question

I'm developing a distributed system with microservices with only one write-only database and the rest read-only in logical replication

My challenge is to have consistency in the data, especially in the balance that the client has in the account

Since my business model necessarily requires microservices to carry out updates to the database tables, and depending on the situation, a balance is added to the customer's account.

My question is, how to guarantee consistency of the customer's balance if he carries out some process that reduces his account balance at the same time that some microservice is also manipulating his account balance

I thought about creating a transaction in the database and immediately using this transaction to change its balance while the system finishes processing updates in other tables that are part of the process, as this blocks the reading of that customer's balance while the transaction is not completed. finished, is this the most correct way?

• mustaccio: This: How to atomically update different databases? talks about different databases, but the principle is the same, as you cannot span a single database transaction across multiple sessions created by multiple microservices.
• Miqueias Kevison: Unfortunately this would not apply in my case because I only use one database for writing, the others are read-only
• mustaccio: As I said, the number of databases doesn't matter; the number of separate sessions constituting a single business transaction does.

Answer 1

After some research, I found solutions, all natively offered by PostgreSQL.

The first one is the transaction isolation level Repeatable Read:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

This isolation level ensures that once a transaction has read a set of data, that data will remain consistent, even if other transactions modify it later.

Even if other users modify the data during the transaction, those modifications will not be visible within the current transaction.

If a transaction attempts to save data that has been modified (committed) by another transaction while it was in progress at the "Repeatable Read" isolation level, this will result in a concurrency error or isolation violation. And you just need to repeat the process from the beginning.

SELECT FOR UPDATE

The SELECT ... FOR UPDATE command is a clause in PostgreSQL that allows a user to explicitly lock rows of a table for update (or read if the other transaction also uses SELECT FOR UPDATE). This is useful in scenarios of concurrency control,

Optimistic Locking

Optimistic locking is a technique used in databases to allow multiple transactions to access the same data at the same time without blocking each other. Instead of blocking the data while it is being modified, optimistic locking relies on checking data versions to avoid conflicts.

Imagine you are updating a record in a database that has a version control column and a customer's balance column. Before performing the update, you check if the version of the record is the same as when you initially read it. If the version is still the same, you apply the update. Otherwise, this means another transaction has already modified the data, and you need to handle that situation, perhaps by reloading the data and redoing the operation or notifying the user about the conflict.

Here's a simple example with two transactions:

Transaction 1:

1. Reads a record from the database.
2. The record has ID 1 and version 1.
3. Initiates the update of the record.
4. Executes an UPDATE table SET version = 2, amount = 100 WHERE id = 1 AND version = 1.
5. Checks the number of rows affected by the UPDATE. If it's 1, the update was successful.

Ends the transaction.

Transaction 2 (started after the read, but before the update by Transaction 1):

1. Reads the same record from the database.
2. The record has ID 1 and version 1.
3. Initiates an update of the same record.
4. Executes an UPDATE table SET version = 2, amount = 50 WHERE id = 1 AND version = 1.
5. Checks the number of rows affected by the UPDATE. In this case, it will be 0 because the previous transaction has already altered this data.
6. Handles the conflict situation (such as notifying the user or redoing the process).