Re: I think that the transaction tutorial document (3.4) should mention transaction isolation

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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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