Re: Transaction Isolation level for ERP software - Mailing list pgsql-general
| From | Justin |
|---|---|
| Subject | Re: Transaction Isolation level for ERP software |
| Date | |
| Msg-id | CALL-XeMFTGBS7vjRE2E1JJSxO2OEUOmER2euBhEkQytJPMZcZw@mail.gmail.com Whole thread |
| List | pgsql-general |
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.
--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
pgsql-general by date: