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

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/tutorial-transactions.html
Description:

Hi,

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.

Thanks,
Noam

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.