Thread: Mistake in statement example

Mistake in statement example

From

PG Doc comments form

Date:

01 March 2023, 10:21:30

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/transaction-iso.html
Description:

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.

Re: Mistake in statement example

From

Tom Lane

Date:

01 March 2023, 19:34:11

PG Doc comments form <noreply@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.

            regards, tom lane

Re: Mistake in statement example

From

"David G. Johnston"

Date:

01 March 2023, 19:45:00

On Wed, Mar 1, 2023 at 9:34 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG Doc comments form <noreply@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.

David J.

Re: Mistake in statement example

From

Bruce Momjian

Date:

28 September 2023, 02:23:09

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 <tgl@sss.pgh.pa.us> wrote:
> 
>     PG Doc comments form <noreply@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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Attachment

mvcc.diff