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?
