Re: Transaction Isolation level for ERP software

On Wed, Jul 15, 2026 at 3:08 PM Justin <zzzzz(dot)graf(at)gmail(dot)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/set-transaction#traditional-locking-behavior
> 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