Re: Transaction Isolation level for ERP software

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 ;

>
>