On Wed, Mar 1, 2023 at 09:45:00AM -0700, David G. Johnston wrote:
> On Wed, Mar 1, 2023 at 9:34 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> PG Doc comments form <nore...@postgresql.org> writes:
> > I believe there is a mistake in an example on
> > https://www.postgresql.org/docs/current/transaction-iso.html section
> > 13.2.1:
> > BEGIN;
> > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> > UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
> > COMMIT;
>
> > The acctnum is expected to be 12345 in both cases.
>
> No, I think that's intentional: the example depicts transferring
> $100 from account 7534 to account 12345.
>
>
>
> That may be, but the descriptive text and point of the example (which isn't
> atomicity, but concurrency) doesn't even require the second update command to
> be present. What the example could use is a more traditional two-session
> depiction of the commands instead of having a single transaction and letting
> the user envision the correct concurrency.
>
> Something like:
>
> S1: SELECT balance FROM accounts WHERE acctnum = 12345; //100
> S1: BEGIN;
> S2: BEGIN;
> S1: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
> //200
> S2: UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; //
> WAITING ON S1
> S1: COMMIT;
> S2: UPDATED; balance = 300
> S2: COMMIT;
>
> Though maybe "balance" isn't a good example domain, the incrementing example
> used just after this one seems more appropriate along with the added benefit
> of
> consistency.
I developed the attached patch. I explained the example, I mentioned a
"second" transaciton, I changed the account number so I can talk about
the second statement, because read committed changes the row visibility
of the non-first statements, and I changed "transaction" to "statement".
--
Bruce Momjian <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index f8f83d4..189cab0
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 413,420 ****
does not see effects of those commands on other rows in the database.
This behavior makes Read Committed mode unsuitable for commands that
involve complex search conditions; however, it is just right for simpler
! cases. For example, consider updating bank balances with transactions
! like:
<screen>
BEGIN;
--- 413,420 ----
does not see effects of those commands on other rows in the database.
This behavior makes Read Committed mode unsuitable for commands that
involve complex search conditions; however, it is just right for simpler
! cases. For example, consider transferring $100 from one account
! to another:
<screen>
BEGIN;
*************** UPDATE accounts SET balance = balance -
*** 423,430 ****
COMMIT;
</screen>
! If two such transactions concurrently try to change the balance of account
! 12345, we clearly want the second transaction to start with the updated
version of the account's row. Because each command is affecting only a
predetermined row, letting it see the updated version of the row does
not create any troublesome inconsistency.
--- 423,430 ----
COMMIT;
</screen>
! If another transactions concurrently tries to change the balance of account
! 7534, we clearly want the second statement to start with the updated
version of the account's row. Because each command is affecting only a
predetermined row, letting it see the updated version of the row does
not create any troublesome inconsistency.
