PostgreSQL Row Locks in Formance

Last updated: July 24, 2025

In Formance, every transaction involves writing changes to the accounts_volumes table. Even if your account has overdraft enabled (i.e., no balance check), PostgreSQL still applies row-level locks when updating balances.

Let’s break it down:

overdraft not allowed
- Formance proactively issues:
```
SELECT * FROM accounts_volumes WHERE account = 'xyz' FOR UPDATE;
```
- This does two things:
  - Explicitly locks the row before doing any validation.
  - Ensures other transactions trying to read or lock the same row are blocked at that point.
overdraft enabled
- When a transaction updates the volume of an account, the SQL statement typically looks like:
```
UPDATE accounts_volumes SET ... WHERE account = 'xyz';
```
- During the UPDATE, PostgreSQL:
  - Locates the row matching account = 'xyz'.
  - Acquires a row-level exclusive lock on that row. PostgreSQL uses this lock to prevent dirty writes and ensure ACID compliance—even if no explicit SELECT ... FOR UPDATE is issued.
  - Writes a new version of the row (PostgreSQL is MVCC-based).

The row-level lock acquired by PostgreSQL during an UPDATE lasts for the entire duration of the SQL transaction, not just during the execution of the UPDATE statement itself. This lock is held until the transaction is COMMIT or ROLLBACK.

So to clarify:

Use Case	Locking Mechanism
Overdraft allowed (e.g., `world`)	Lock occurs implicitly during `UPDATE`
Overdraft not allowed	Lock occurs via `SELECT ... FOR UPDATE`, plus the `UPDATE`

In both cases, concurrent writes to the same account row create contention, they become serialized — they have to wait for the lock to be released.

It's especially easy to fall into this trap with world account, since it's often used as a universal source.