Hello David, I have attached a proposed doc update that makes the problem clearer. I think that this is important because if people do not understand it they will write buggy code and then blame Postgresql for losing updates, which is totally unacceptable. So please do action this. I have tested and confirm that the behaviour is as I specify.
There is more that could be said, e.g. why to avoid SHARE locks. But I think that that is enough. (I personally think that the default semantics are very dubious. Either Repeatable Read should be the default mode, or updating a row already read but changed should produce an error for Read Committed. The goal is not to satisfy academic rules but produce something that works safely in practice. But I am sure there has been much discussion about that elsewhere.) On Fri, Jul 30, 2021 at 6:03 PM Anthony Berglas <anth...@berglas.org> wrote: > OK, I'll put something for you to review. Most programmers simply ignore > locking and wonder why it sometimes goes wrong. > > On Fri, Jul 30, 2021 at 2:22 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Thu, Jul 29, 2021 at 8:44 PM Anthony Berglas <anth...@berglas.org> >> wrote: >> >>> My point is that while I can follow the academic style discussion, most >>> of my colleagues could not. They just need to have a clear idea of how to >>> handle the common case, which is to use a database using some programming >>> language. >>> >> >> On the whole I believe that we are both mostly correct in our >> observations. I would be happy to review a change to this section of the >> documentation - whether done surgically or in a larger patch to make this >> resource much more accessible to users. I don't plan to take on the >> activity of putting together an initial patch for consideration. I will >> observe that there haven't been many questions or comments pertaining to >> this material hitting the mailing lists; though why that may be is >> difficult to guess. Maybe it's covered better in books so people just >> don't use this as a resource for the topic? In any case, the ability for >> someone that knows this material well, but is a coder and not a teacher, to >> write up something better and more accessible, and judging this is be a >> better use of their time than some other activity, is fairly low. It does >> seem, however, like an excellent project for someone who benefits from the >> open source nature of the project and is looking for a way to both give >> back to the community and learn a topic more fully at the same time. >> >> David J. >> >> >> >>Title: Posgresql Locking Docs
13.2.1. Read Committed Isolation LevelReplace "because of the above rules" and the example with the following:-Because of the above rules, transactions can produce incorrect values if the FOR UPDATE clause is not included on any SELECT statement that is used to select data that will subsequently be used to UPDATE. In the following example, a stock purchase transaction B of 100 units happens during a stock sale transaction A of 50 units. However, transaction A overwrites the update made by transaction B because it did not lock product 123 using SELECT ... FOR UPDATE, so the resulting quantiy_on_hand is wrong.
The current docs say that statement 2 is OK, which implies that the Update statement is atomic, i.e. that no other transaction can split the read part of the update from the write part. I suspect that that is not true, and no mention should be made in the docs unless it is really known to be true. 13.2.2. Repeatable Read Isolation LevelBefore the paragraph "The Repeatable Read mode provides" addThese concurrent update errors can be minimized (if not avoided entirely) by adding the FOR UPDATE clause to any SELECT statements for any rows that are retrieved and likely to be updated. That will cause the transaction to wait until it can obtain and exclusive lock on the row, and then prevent any other transaction from reading the row. |
