Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wk5sn-000Sw6-0U for pgsql-general@arkaria.postgresql.org; Wed, 15 Jul 2026 20:07:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wk5sk-009PMz-1E for pgsql-general@arkaria.postgresql.org; Wed, 15 Jul 2026 20:07:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wk5sk-009PMr-02 for pgsql-general@lists.postgresql.org; Wed, 15 Jul 2026 20:07:02 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wk5sh-00000000SeJ-2fRn for pgsql-general@lists.postgresql.org; Wed, 15 Jul 2026 20:07:01 +0000 Received: by mail-ed1-x530.google.com with SMTP id 4fb4d7f45d1cf-69ccd6483acso3346424a12.3 for ; Wed, 15 Jul 2026 13:06:59 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1784146019; cv=none; d=google.com; s=arc-20260327; b=BTFIwW+qeM0+Q7WbhrhUiXShkVYIjXb5wUn63hBsjR5fg5XUlQPG1qTNZvjEUnyL8U PxPyP/QRcmWzOilL5DbRhFtrVgtgdtZz5bxVLLWiErm5cp1imsKLhLe4JjYKPhkhuHyv yDVfbBhNB2xW4wxp+H6tWt1CV2MOuFEgpAxSsm0AGUNFEUfLp2i4r5jcwwSACPQzQPbL cjceS7edBRgleiEK092aKpWeuv8yBiEp44h6yt5cXzGKLoBcMzjSbcwyhnasCe/+EOP7 8C+5rgJLWFR4x/9gzrLYQYtmSjD5uztzKFpdCdo/Sk/tm7e7mwTFPL2Gmq0R5Wt1+QVf kjrQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20260327; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=TyUrwshRT9/9+6K/+dlnxN6vCsNddTb/TSj8MEq05ms=; fh=hMas4DmliwJkHrHnjD9n2xGMa/KrdA2MVY+jbj9GA/o=; b=lDSB0FYFKA659bIeqBpz6mBKwkebgbWjwoHBBqKo8zjDLD+I5N1GTou9Z4mcPuB2CY lLOqkU45ntVV/IG0knbDURRHl2iOQ3OXb8siI1LqNfOyIJDZq32Q6Gpw+O8rkNyYhqrF 4PPWvjlqce7RkgTqPHw0bnYipn3sSKi8NDbETk6kjhUXcQfZ1nXRkwuKx2yrxmQ2B0i6 oWOaBK2V7QrgQFc5Nx80gLacTBPNOSBqo0ykC2/d5O0w3633Vb+1tMnKvqlylCC9qj4e xEsEmXTe5y366yQl4kIR2rWABYOlFgervLQZ3yH/wCt6Tiw5Ty0lJTmd7p/g3/HDaRNq 8nmw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1784146019; x=1784750819; darn=lists.postgresql.org; h=content-type:cc:to:subject:message-id:date:from:in-reply-to :references:mime-version:from:to:cc:subject:date:message-id:reply-to :content-type; bh=TyUrwshRT9/9+6K/+dlnxN6vCsNddTb/TSj8MEq05ms=; b=JuKzaNMoE3srW5TJDRK+/6Q8AwsRFEDk4rMLQQlFPeoyYVpAc+Mfi5/Y6oIZnKvxdF 5ZNMpM+ZDNinGP0GlHmKFC2FYxbmKGix7o8pF0JIDfGBgnJNOwzgI4oXHsFAFvWS+h7z 8znwCSIbCMjJH3lwDS4SbsF2gnyfmSk0i21WN46gmXusUDx6zvAutRFw05B5IX0Fpby7 0qSQkMq/Nj4TQWb9usRXdTw1EOl7yFKe/d4n5aPFVvqxoQoSBs60ESyA3HkwscV09QPW Ku65uePhf5HaWsx94xS0q5SmgpUZYsuoOqzX6i84ZQekHfQ4UfFGIrJxMiGO6KJSvpyu 4ClA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1784146019; x=1784750819; h=content-type:cc:to:subject:message-id:date:from:in-reply-to :references:mime-version:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to:content-type; bh=TyUrwshRT9/9+6K/+dlnxN6vCsNddTb/TSj8MEq05ms=; b=s0pcFdTK5zPUbxPQTVZhxy5bRS+5EjGp36Vd6wL99W+iNHGAbIvrNmCWoXCxlpFNvx wqkI2l+thyUrjlua6EX+ufAe2TF+cQnD2AxCOGNkqf5ohak+tra6iPCKT+qulVVjoFPl gs1jP56FeEmNwOc62Z1LiAMD+OAME33lHIEQTRythUXLZQy04I1kbwHVVzxFIftc7kAe YHbeIx4aljveEx+u303I/EizbqzPr7EUr2siySNrAvZha/R9AkC0InpWUy5ok4SY2989 SiZqcCv6eLRE3WutUsuwk9wXk+2P+WbfgaKTtuqkjU+bg9Q+vVV/MJWZS7EutT4HcPi8 Hy7w== X-Gm-Message-State: AOJu0YwMMbAs4EhoTHL4M1P+MbqyM2A9PyuI8nbMBedTH8aYPjJZ8eEk EZmmbsrAQ50L2UhTUcs9lhYDwJUKrb+SslYWHcOJuXiiTPHaLhfEHaZ1Qyods6pxmXyB7O3KwK6 vH+NrBq8cSeXfLv//jlQZygu1yP3+QADtb/Kr X-Gm-Gg: AfdE7ckvVv4XiWx/axX/QnI2bfQre3wja+qNroJ4FZtwxWTrwXsl9+w6GVCAWyokMps 6v0xPMWVmwvnNznApGRykqkFQoO7TESc8lFj4+KYveKM1Gpk5v8xkhe3os8VEy4YwL2TCNM+9ES E5iVg4aU+z6cbC0tKy8BNDxd3h8zNO0BdEbqEotpvvLRYj/uV3NnAyxVS15E/vEMjNxC5wJCpfX THhP00K6kHya+ZajFB8vqrPwEYhmMvesCwUgCs5TDpJiJQ3WXfcW+w3QpRAqRUU2x5fcz2FPr+X QwmJ1Ruxrz6yVsd02g1LHC5jN5v6HjyMaocgo018+tsPMJEl9ucLWqIm X-Received: by 2002:a05:6402:5188:b0:69a:a6f1:539c with SMTP id 4fb4d7f45d1cf-69cd6c9fafcmr4452389a12.2.1784146018388; Wed, 15 Jul 2026 13:06:58 -0700 (PDT) MIME-Version: 1.0 References: <23ce3f85-6f66-4724-a2e7-7e13e077d423@gmx.net> In-Reply-To: From: Justin Date: Wed, 15 Jul 2026 16:06:45 -0400 X-Gm-Features: AUfX_mzk4SEqbJrzjjypSSgbq74OJxXf4jt8RyrIhs12VkZHgxrrCRUJQ2tYjOw Message-ID: Subject: Re: Transaction Isolation level for ERP software To: shammat@gmx.net Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a74f310656abdd83" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a74f310656abdd83 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jul 15, 2026 at 3:08=E2=80=AFPM Justin 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 th= e > 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, develope= rs > cannot legitimately claim that REPEATABLE READ is being used when the > database is allowed to commit transactions that violate serialization rul= es. > > Manual Reference: > https://mariadb.com/docs/server/reference/sql-statements/administrative-s= ql-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 =3D id + 1 where id =3D1; --change to a r= eal > transaction throws exception > Commit ; > > --session 2 > > Begin ISOLATION LEVEL REPEATABLE READ; > update trans_isolation set id =3D -1 where id =3D1; ---creates a real > transactions any prior transactions still open in REPEATABLE READ mode wi= ll > 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 =3D id + 1 where id =3D1; -- DOES NOT thro= w the > exception > Commit ; > > --session 2 > Begin ; > update trans_isolation set id =3D -1 where id =3D1; > 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 =3D 123; -- Reads 100 -- (does other work...) --Session 2: BEGIN; UPDATE inventory SET quantity =3D 80 WHERE item_id =3D 123; COMMIT; ---Session 1: UPDATE inventory SET quantity =3D quantity - 10 WHERE item_id =3D 123; COMMIT; Session 1 overwrote Session 2=E2=80=99s update. The inventory quantity is n= ow 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 --000000000000a74f310656abdd83 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=C2=A0

On We= d, Jul 15, 2026 at 3:08=E2=80=AFPM Justin <zzzzz.graf@gmail.com> wrote:

MariaDB's snapshot isolation works similarly to PostgreSQL, b= ut 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 R= EAD COMMITTED, it will throw serialization errors on conflicts. This behavi= or follows the SQL standard, and PostgreSQL enforces it strictly and can no= t be turned off. Below are examples demonstrating how these serialization e= rrors manifest.

Disabling snapshot violation error checks in MariaDB effect= ively reduces its transaction isolation level to READ COMMITTED. This means= the system is no longer enforcing the configured isolation level. As a res= ult, developers cannot legitimately claim that REPEATABLE READ is being use= d when the database is allowed to commit transactions that violate serializ= ation rules.

Manual Reference: https://mariadb.com/docs/server/reference/sql-stat= ements/administrative-sql-statements/set-commands/set-transaction#tradition= al-locking-behavior=20 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 str= ongly suggests the application has never properly handled transaction isola= tion. My suggestion is run the application in Read Committed and move to Serializ= ation only when it is absoletly critical.


--session 1 transaction= :
Begin ISOLATION LEVEL REPEATABLE READ;
SELECT * from trans_isolatio= n ; =C2=A0---create a virtual transaction ID;
--stop here execute sessio= n 2,
UPDATE trans_isolation set id =3D id + 1 where id =3D1; =C2=A0--cha= nge to a=C2=A0 real transaction=C2=A0 throws exception
Commit ;
--session 2

Begin ISOLATION LEVEL REPEATABLE READ;
update trans_= isolation set id =3D -1 where id =3D1;=C2=A0 ---creates a=C2=A0 real transa= ctions any prior transactions still open in REPEATABLE READ mode will throw= a error now
Commit ;

----Repeat the process in for read committe= d postgresql default isolation level

--session 1 transaction:
Beg= in ;
SELECT * from trans_isolation ; =C2=A0---create a virtual transacti= on ID;
--stop here execute session 2,
UPDATE trans_isolation set id = =3D id + 1 where id =3D1; =C2=A0-- DOES NOT throw the exception =C2=A0
C= ommit ;

--session 2
Begin ;
update trans_isolation set id =3D= -1 where id =3D1;
Commit ;=C2=A0


Forgot to mention this.

READ COMMITTED wi= th explicit locking is not equivalent to REPEATABLE READ o= r SERIALIZABLE.

Because of MVCC, writers = do not block readers, and readers do not block writers. This means a transa= ction can read data at one point in time and later act on stale data.=C2=A0

--Session 1:

BEGIN;
SELECT quantity FROM inventory= WHERE item_id =3D 123; =C2=A0 -- Reads 100
-- (does other work...)
<= br>--Session 2:

BEGIN;
UPDATE inventory SET quantity =3D 80 WHERE= item_id =3D 123;
COMMIT;

---Session 1:
UPDATE inventory
S= ET quantity =3D quantity - 10
WHERE item_id =3D 123;
COMMIT;

= Session 1 overwrote Session 2=E2=80=99s update. The inventory quantity is n= ow incorrect (lost update).

Advisory locks can reduce this risk, but= the application must correctly acquire and release them everywhere they ar= e needed.

PostgreSQL generally favors optimistic approaches over he= avy pessimistic locking, because manual row-level locking often leads to hi= gh contention, lock waits, and performance problems.

REPEATABLE READ= and SERIALIZABLE solve these=C2=A0problems differently. The database ensur= es that when a transaction attempts to write, any later transaction update = is detected, the transaction fails with a serialization error instead of si= lently losing an update.

For most operations, READ COMMITTED=C2=A0 w= orks. For critical transactions where lost updates would cause serious prob= lems, you have two=C2=A0 options:

Use REPEATABLE READ (or SERIALIZAB= LE) only for those specific transactions, and implement retry logic=C2=A0 w= hen serialization errors occur.

Critical ERP Transactions that need= =C2=A0 isolation

Inventory quantity updates
Inventory valuation u= pdates
Rollups and rollovers (period-end processing)
General ledger a= djustments (ideally the application should prevent editing posted GL entrie= s entirely)
Committing serialized/lot-tracked items to shipments or prod= uction orders

These operations are high-risk for lost updates or inc= onsistent state if concurrent modifications occur.

Hope this helps
--000000000000a74f310656abdd83--