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
