pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedTransaction Isolation level for ERP software
10+ messages / 7 participants
[nested] [flat]
* Transaction Isolation level for ERP software
@ 2026-07-15 17:34 Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 17:52 ` Re: Transaction Isolation level for ERP software Christophe Pettus <xof@thebuild.com>
2026-07-15 17:56 ` Re: Transaction Isolation level for ERP software Adrian Klaver <adrian.klaver@aklaver.com>
2026-07-15 18:04 ` Re: Transaction Isolation level for ERP software shammat@gmx.net
2026-07-15 21:20 ` Re: Transaction Isolation level for ERP software Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-17 17:15 ` Re: Transaction Isolation level for ERP software Merlin Moncure <mmoncure@gmail.com>
0 siblings, 5 replies; 10+ messages in thread
From: Mihir Kandoi @ 2026-07-15 17:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I work on an ERP software called ERPNext <https://github.com/frappe/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. Given the number of tickets and the severity of
an ERP software not functioning ideally, along with constant nagging from
enterprise customers, we temporarily disabled snapshot isolation.
Now with Postgres and REPEATABLE READ , there is no option like MariaDB to
just turn off snapshot violation errors. We believe that once Postgres
support hits production, we will again face another set of similar
serialization errors.
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.
4.
Most business apps use REPEATABLE READ as an industry standard.
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.
2.
I have also implemented Advisory Locks to counter this problem but I
don't know how effective that will actually be.
3.
Claude and ChatGPT also both suggest READ COMMITTED as well.
4.
Is READ COMMITTED actually a better solution or should we go with
retrying transactions?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
@ 2026-07-15 17:52 ` Christophe Pettus <xof@thebuild.com>
4 siblings, 0 replies; 10+ messages in thread
From: Christophe Pettus @ 2026-07-15 17:52 UTC (permalink / raw)
To: Mihir Kandoi <kandoimihir@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
> On Jul 15, 2026, at 10:34, Mihir Kandoi <kandoimihir@gmail.com> wrote:
> • Is READ COMMITTED actually a better solution or should we go with retrying transactions?
There's nothing specific about ERP platforms regarding the transaction isolation mode, and READ COMMITTED isn't "better" than REPEATABLE READ or vice versa. It's a matter of what semantics you want for transactions: do you want a persistent snapshot that does not change (in from the perspective of the session with the open transaction) between statements, or can your application tolerate that?
In general, you want to use the weakest transaction isolation mode that gives you the semantics that you want. If your application is written to expect that the snapshot of the database does not change during the course of the transaction, you'll need REPEATABLE READ, and then need to come up with a solution for the serialization errors that you receive.
The source of serialization failures in REPEATABLE READ is two sessions attempting to update the same rows. That's probably the issue you should consider tracking down, since that has plenty of other potential problems (lock waits, deadlocks) apart from REPEATABLE READ.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
@ 2026-07-15 17:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
4 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2026-07-15 17:56 UTC (permalink / raw)
To: Mihir Kandoi <kandoimihir@gmail.com>; pgsql-general@lists.postgresql.org
On 7/15/26 10:34 AM, Mihir Kandoi wrote:
> I work on an ERP software called ERPNext <https://github.com/frappe/
> 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. Given the number of tickets
> and the severity of an ERP software not functioning ideally, along with
> constant nagging from enterprise customers, we temporarily disabled
> snapshot isolation.
This article:
https://www.percona.com/blog/mariadbs-snapshot-isolation-a-fix-that-breaks-more-than-it-fixes/
seems to be a good explanation of what is actually happening, namely it
is not actually a fix.
>
> Now with Postgres and |REPEATABLE READ| , there is no option like
> MariaDB to just turn off snapshot violation errors. We believe that once
> Postgres support hits production, we will again face another set of
> similar serialization errors.
Set up a Postgres test database and run your problematic queries against
it and then you can go from 'believe' to know.
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
@ 2026-07-15 18:04 ` shammat@gmx.net
2026-07-15 19:08 ` Re: Transaction Isolation level for ERP software Justin <zzzzz.graf@gmail.com>
4 siblings, 1 reply; 10+ messages in thread
From: shammat@gmx.net @ 2026-07-15 18:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
> 3. READ COMMITTED has its own set of problems like gap locks
I have to admit that my knowledge about MariaDB's "gap locks" is a bit fuzzy,
but I don't think Postgres has anything similar.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 18:04 ` Re: Transaction Isolation level for ERP software shammat@gmx.net
@ 2026-07-15 19:08 ` Justin <zzzzz.graf@gmail.com>
2026-07-15 20:06 ` Re: Transaction Isolation level for ERP software Justin <zzzzz.graf@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Justin @ 2026-07-15 19:08 UTC (permalink / raw)
To: shammat@gmx.net; +Cc: pgsql-general@lists.postgresql.org
MariaDB's snapshot isolation works similarly to PostgreSQL, but there are a
few subtle differences. See the PostgreSQL documentation for reference:
https://www.postgresql.org/docs/current/transaction-iso.html
When running PostgreSQL in any isolation level other than READ COMMITTED,
it will throw serialization errors on conflicts. This behavior follows the
SQL standard, and PostgreSQL enforces it strictly and can not be turned
off. Below are examples demonstrating how these serialization errors
manifest.
Disabling snapshot violation error checks in MariaDB effectively reduces
its transaction isolation level to READ COMMITTED. This means the system is
no longer enforcing the configured isolation level. As a result, developers
cannot legitimately claim that REPEATABLE READ is being used when the
database is allowed to commit transactions that violate serialization rules.
Manual Reference:
https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/...
This situation also highlights a significant design flaw in the
application: it updates the same records multiple times across different
sessions and transactions, which creates race conditions throwing these
errors. This strongly suggests the application has never properly handled
transaction isolation. My suggestion is run the application in Read
Committed and move to Serialization only when it is absoletly critical.
--session 1 transaction:
Begin ISOLATION LEVEL REPEATABLE READ;
SELECT * from trans_isolation ; ---create a virtual transaction ID;
--stop here execute session 2,
UPDATE trans_isolation set id = id + 1 where id =1; --change to a real
transaction throws exception
Commit ;
--session 2
Begin ISOLATION LEVEL REPEATABLE READ;
update trans_isolation set id = -1 where id =1; ---creates a real
transactions any prior transactions still open in REPEATABLE READ mode will
throw a error now
Commit ;
----Repeat the process in for read committed postgresql default isolation
level
--session 1 transaction:
Begin ;
SELECT * from trans_isolation ; ---create a virtual transaction ID;
--stop here execute session 2,
UPDATE trans_isolation set id = id + 1 where id =1; -- DOES NOT throw the
exception
Commit ;
--session 2
Begin ;
update trans_isolation set id = -1 where id =1;
Commit ;
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 18:04 ` Re: Transaction Isolation level for ERP software shammat@gmx.net
2026-07-15 19:08 ` Re: Transaction Isolation level for ERP software Justin <zzzzz.graf@gmail.com>
@ 2026-07-15 20:06 ` Justin <zzzzz.graf@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Justin @ 2026-07-15 20:06 UTC (permalink / raw)
To: shammat@gmx.net; +Cc: pgsql-general@lists.postgresql.org
On Wed, Jul 15, 2026 at 3:08 PM Justin <zzzzz.graf@gmail.com> wrote:
> MariaDB's snapshot isolation works similarly to PostgreSQL, but there are
> a few subtle differences. See the PostgreSQL documentation for reference:
> https://www.postgresql.org/docs/current/transaction-iso.html
>
> When running PostgreSQL in any isolation level other than READ COMMITTED,
> it will throw serialization errors on conflicts. This behavior follows the
> SQL standard, and PostgreSQL enforces it strictly and can not be turned
> off. Below are examples demonstrating how these serialization errors
> manifest.
>
> Disabling snapshot violation error checks in MariaDB effectively reduces
> its transaction isolation level to READ COMMITTED. This means the system is
> no longer enforcing the configured isolation level. As a result, developers
> cannot legitimately claim that REPEATABLE READ is being used when the
> database is allowed to commit transactions that violate serialization rules.
>
> Manual Reference:
> https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/...
> This situation also highlights a significant design flaw in the
> application: it updates the same records multiple times across different
> sessions and transactions, which creates race conditions throwing these
> errors. This strongly suggests the application has never properly handled
> transaction isolation. My suggestion is run the application in Read
> Committed and move to Serialization only when it is absoletly critical.
>
> --session 1 transaction:
> Begin ISOLATION LEVEL REPEATABLE READ;
> SELECT * from trans_isolation ; ---create a virtual transaction ID;
> --stop here execute session 2,
> UPDATE trans_isolation set id = id + 1 where id =1; --change to a real
> transaction throws exception
> Commit ;
>
> --session 2
>
> Begin ISOLATION LEVEL REPEATABLE READ;
> update trans_isolation set id = -1 where id =1; ---creates a real
> transactions any prior transactions still open in REPEATABLE READ mode will
> throw a error now
> Commit ;
>
> ----Repeat the process in for read committed postgresql default isolation
> level
>
> --session 1 transaction:
> Begin ;
> SELECT * from trans_isolation ; ---create a virtual transaction ID;
> --stop here execute session 2,
> UPDATE trans_isolation set id = id + 1 where id =1; -- DOES NOT throw the
> exception
> Commit ;
>
> --session 2
> Begin ;
> update trans_isolation set id = -1 where id =1;
> Commit ;
>
>>
>>
*Forgot to mention this.*
READ COMMITTED with explicit locking is *not* equivalent to REPEATABLE READ
or SERIALIZABLE.
Because of MVCC, writers do not block readers, and readers do not block
writers. This means a transaction can read data at one point in time and
later act on stale data.
--Session 1:
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 123; -- Reads 100
-- (does other work...)
--Session 2:
BEGIN;
UPDATE inventory SET quantity = 80 WHERE item_id = 123;
COMMIT;
---Session 1:
UPDATE inventory
SET quantity = quantity - 10
WHERE item_id = 123;
COMMIT;
Session 1 overwrote Session 2’s update. The inventory quantity is now
incorrect (lost update).
Advisory locks can reduce this risk, but the application must correctly
acquire and release them everywhere they are needed.
PostgreSQL generally favors optimistic approaches over heavy pessimistic
locking, because manual row-level locking often leads to high contention,
lock waits, and performance problems.
REPEATABLE READ and SERIALIZABLE solve these problems differently. The
database ensures that when a transaction attempts to write, any later
transaction update is detected, the transaction fails with a serialization
error instead of silently losing an update.
For most operations, READ COMMITTED works. For critical transactions where
lost updates would cause serious problems, you have two options:
Use REPEATABLE READ (or SERIALIZABLE) only for those specific transactions,
and implement retry logic when serialization errors occur.
Critical ERP Transactions that need isolation
Inventory quantity updates
Inventory valuation updates
Rollups and rollovers (period-end processing)
General ledger adjustments (ideally the application should prevent editing
posted GL entries entirely)
Committing serialized/lot-tracked items to shipments or production orders
These operations are high-risk for lost updates or inconsistent state if
concurrent modifications occur.
Hope this helps
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
@ 2026-07-15 21:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-16 08:36 ` Re: Transaction Isolation level for ERP software shammat@gmx.net
4 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2026-07-15 21:20 UTC (permalink / raw)
To: Mihir Kandoi <kandoimihir@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 21:20 ` Re: Transaction Isolation level for ERP software Laurenz Albe <laurenz.albe@cybertec.at>
@ 2026-07-16 08:36 ` shammat@gmx.net
2026-07-16 14:53 ` Re: Transaction Isolation level for ERP software Justin <zzzzz.graf@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: shammat@gmx.net @ 2026-07-16 08:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Laurenz Albe schrieb am 15.07.2026 um 23:20:
>> 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.
I think, this viewpoint might be based on the fact that it's the default isolation level in MySQL/MariaDB
and due to their "marked share", it might be considered a an "industry standard" by a lot of people.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 21:20 ` Re: Transaction Isolation level for ERP software Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-16 08:36 ` Re: Transaction Isolation level for ERP software shammat@gmx.net
@ 2026-07-16 14:53 ` Justin <zzzzz.graf@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Justin @ 2026-07-16 14:53 UTC (permalink / raw)
To: shammat@gmx.net; +Cc: pgsql-general@lists.postgresql.org, Mihir Kandoi <kandoimihir@gmail.com>
On Thu, Jul 16, 2026 at 4:36 AM <shammat@gmx.net> wrote:
> Laurenz Albe schrieb am 15.07.2026 um 23:20:
> >> 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.
>
> I think, this viewpoint might be based on the fact that it's the default
> isolation level in MySQL/MariaDB
> and due to their "marked share", it might be considered a an "industry
> standard" by a lot of people.
>
>
MariaDB Repeatable Read implementation is NOT compliant to the SQL
standard only recently has it gotten closer hence the serialization
errors the original poster is getting with the later versions of MariaDB
https://jepsen.io/analyses/mysql-8.0.34 very good article
Many people believe they are operating in repeatable read when in reality
the database is acting in really odd ways that no one would expect.
Why MySQL/MariaDB chose repeatable read isolation as the default when every
other database uses Read Committed by default. I do not know MySQL is the
odd ball because all the other major databases going back 20+ years use
Read Committed.
Mssql:
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?...
Orcale: https://docs.oracle.com/cd/B13789_01/server.101/b10743/consist.htm
PG https://www.postgresql.org/docs/current/transaction-iso.html
DB2: https://www.ibm.com/docs/en/db2/11.5.x?topic=issues-isolation-levels
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Transaction Isolation level for ERP software
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
@ 2026-07-17 17:15 ` Merlin Moncure <mmoncure@gmail.com>
4 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2026-07-17 17:15 UTC (permalink / raw)
To: Mihir Kandoi <kandoimihir@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Wed, Jul 15, 2026 at 11:34 AM Mihir Kandoi <kandoimihir@gmail.com> wrote:
> I work on an ERP software called ERPNext
> <https://github.com/frappe/erpnext;. Currently, we use MariaDB with REPEATABLE
> READ . We have been working on adding Postgres support to it but we have
> reached a roadblock.
>
> 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.
>
retrying is generally the preferred approach -- what is your specific
concern?
> 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.
> 2.
>
> I have also implemented Advisory Locks to counter this problem but I
> don't know how effective that will actually be.
>
> Advisory locks are very strong if you want to do things like lock screens
or data pending user actions as the old school systems used to do with
COBOL locking statements. What I'm sniffing out here is that you might be
using a poor pattern, specifically, opening a transaction and waiting for
the user to do something. I do not recommend this, but adv locks are
perfect for this case, and in fact were significantly improved for ERP type
patterns.
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-07-17 17:15 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-15 17:34 Transaction Isolation level for ERP software Mihir Kandoi <kandoimihir@gmail.com>
2026-07-15 17:52 ` Christophe Pettus <xof@thebuild.com>
2026-07-15 17:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-07-15 18:04 ` shammat@gmx.net
2026-07-15 19:08 ` Justin <zzzzz.graf@gmail.com>
2026-07-15 20:06 ` Justin <zzzzz.graf@gmail.com>
2026-07-15 21:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-07-16 08:36 ` shammat@gmx.net
2026-07-16 14:53 ` Justin <zzzzz.graf@gmail.com>
2026-07-17 17:15 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox