Re: Transaction Isolation level for ERP software

On Wed, 2026-07-15 at 23:04 +0530, Mihir Kandoi wrote:
> I work on an ERP software called ERPNext. Currently, we use MariaDB with REPEATABLE READ .
> We have been working on adding Postgres support to it but we have reached a roadblock.
> Recently, we updated MariaDB on our cloud platform from 10.6 to 11.8. After that, we
> received a barrage of support tickets from people complaining of snapshot violation errors.
> [...]
>
> Now with Postgres and REPEATABLE READ , there is no option like MariaDB to just turn off
> snapshot violation errors.

No, because you cannot keep up the guarantees of REPEATABLE READ otherwise, unless you
use read locks.

> We believe that once Postgres support hits production, we will again face another set
> of similar serialization errors.

That is pretty likely.

> Initially, I recommended to use READ COMMITTED but senior engineers at the company shot
> it down, the reason being:
>    1. Our entire codebase is built with REPEATABLE READ in mind.
>    2. If it does not work, debugging issues stemming from READ COMMITTED will be very difficult.
>    3. READ COMMITTED has its own set of problems like gap locks, phantom reads etc.

That are no "gap locks" in PostgreSQL, but READ COMMITTED certainly allows interesting
anomalies, e.g. through the EvalPlanQual mechanism.

>    4. Most business apps use REPEATABLE READ as an industry standard.

I am surprised by that statement.

My experience is that most developers of "business apps" aren't even aware that there
are different transaction isolation levels.

> They instead suggested retrying transactions with jitter but I honestly feel
> READ COMMITTED is actually better suited in general for a highly concurrent ERP like
> ours. Note that we have implemented row locking everywhere it was warranted.
> I am looking for confirmation of my theory from the community.
>    1. I found only 2 ERPs using REPEATABLE READ - Microsoft Dynamic 365 Business
> Central and Odoo. Rest are mostly READ COMMITTED only.

As mentioned above, that doesn't surprise me.

>    2. I have also implemented Advisory Locks to counter this problem but I don't
> know how effective that will actually be.

Advisory lock have no connection to isolation levels.

>    3. Claude and ChatGPT also both suggest READ COMMITTED as well.

Ah, then is must be true.

>    4. Is READ COMMITTED actually a better solution or should we go with retrying
> transactions?

It depends on various factors.

For one, if conflicts are frequent, a pessimistic locking strategy with explicit
row locks and READ COMMITTED is usually better. If you expect few conflicts and
serialization errors, using REPEATABLE READ is a good solution.

The other consideration is your current code base. Rewriting a software that
is designed around REPEATABLE READ to use explicit row locks might be a lot of
work.

Yours,
Laurenz Albe