Re: I think that the transaction tutorial document (3.4) should mention transaction isolation
On Wed, Mar 29, 2023 at 12:56 PM Bruce Momjian wrote: > On Thu, Mar 2, 2023 at 01:55:31PM +, PG Doc comments form wrote: > > > 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. > I'm on the fence, but leaning toward agreeing in principle. The following paragraph from that page does cover the dynamics reasonably well under the default configuration and the tutorial should assume the reader hasn't changed the default. """ So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously. """ That said, the tutorial lacks any forward reference to non-tutorial information for the reader that wants to expand their knowledge in this area. A paragraph mentioning isolation levels, including stating the default that the tutorial was operating under, and where that and the other modes are defined/explained, should be added to the end of that page. David J.
Re: I think that the transaction tutorial document (3.4) should mention transaction isolation
On Thu, Mar 2, 2023 at 01:55:31PM +, 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 https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
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
