Date: Wed, 29 Mar 2023 15:56:00 -0400
From: Bruce Momjian <bruce@momjian.us>
To: noamraph@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: I think that the transaction tutorial document (3.4) should
 mention transaction isolation
Message-ID: <ZCSX0IjJQ2qJMIiK@momjian.us>
References: <167776533152.987840.17852197823152196095@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <167776533152.987840.17852197823152196095@wrigleys.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/ZCSX0IjJQ2qJMIiK%40momjian.us>
Precedence: bulk

On Thu, Mar  2, 2023 at 01:55:31PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> I just discovered that by default, transactions are not entirely isolated. I
> think that the tutorial should mention this.
> 
> The tutorial gives the example of a bank account, where you make two
> commands, one to increase the balance and one to decrease the balance,
> explaining why you need a transaction. The example, in which the command is
> "UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'", would
> indeed be correct with the default transaction isolation. However, if the
> transaction included getting the balance by a program, adding 100 to it, and
> then setting the balance, consistency would not always be kept, since
> another transaction could have modified the balance in between. By just
> reading the tutorial, the reader would have no idea that this may happen.
> 
> I think that a notice saying that for absolute transaction isolation you
> should set the default transaction isolation to serializable, and be
> prepared to have transactions fail, and referring to the more details
> document, would help users prevent consistency bugs.

Well, the point is that the application is _not_ retrieving a value and
setting is +100, but rather having the SQL add 100, which is kind of the
point.  I don't think a tutorial should get into more details than that.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Embrace your flaws.  They make you human, rather than perfect,
  which you will never be.