Hello David, You are talking about optimistic locking, commonly used for web applications where there is no transaction kept open during user think time. A COMMIT between the SELECT and the UPDATE.
This is also what was needed for traditional MySql running only in AutoCommit mode. It requires no locking at all other than atomic statements. And the end user has to re-enter the transaction if it fails due to a conflict. While processing something on the server however, it is nice to be able to use a proper database like Postgresql that does have locking during a transaction. Failures are more difficult to deal with on a server where you cannot just throw failures back to a user, transactions are more complex, and a short wait for a lock is generally not an issue (there should always be a generous timeout). (If you use SET quantity_on_hand = *quantity_on_hand - 50 *then you do not even need the optimistic lock. But that is rarely done in practice using an Object Relational Mapping library.) Once upon a time, tools like Oracle Forms kept database locks during user think time so locking strategies were very important. But client/server web apps cannot work that way, so databases like MySql were useable. But there is still some server processing to be done, so while locking is less important, it is still worth doing properly. And more importantly it is very important that people do not use a SELECT without a FOR UPDATE and introduce subtle, unreproducible threading errors. So please do have the update (or similar) inserted. If you wanted to also talk about optimistic locking that would be fine, but probably not necessary. Thanks, Anthony P.S. Do you know if Postgresql Guarantees that all timestamps are distinct, even if they occur within the same clock tick? (i.e. does it run the clock forward). I have another reason to know that. Using clocks is iffy for synchronization. On Tue, Aug 3, 2021 at 1:26 AM David G. Johnston <david.g.johns...@gmail.com> wrote: > On Sun, Aug 1, 2021 at 11:35 PM Anthony Berglas <anth...@berglas.org> > wrote: > >> 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. >> > > That really isn't a good solution though...a better one is to modify the > update command to be: > > UPDATE products SET quantity_on_hand = qoh - 50 WHERE quantity_on_hand = > qoh; > > Or, even better: > > SELECT ... last_updated INTO lastupdatetimestamp, ...; > > UPDATE products SET quantity_on_hand = qoh - 50 AND last_updated = > lastupdatetimestamp; > > Then the application needs to simply check for a zero record update and, > if that happens, decide how it wants to deal with the fact that the data > has changed out from under it. > > This is superior to waiting an indeterminate amount of time holding a FOR > UPDATE lock in an open transaction. > > I would still expand on the FOR UPDATE option as you suggest. > > This is still just discussion though, someone will need to convert this > into a proper doc patch that can be built, added to the commitfest, > reviewed, and ultimately committed. IMO it is not worth going to the > trouble of making this all HTML-friendly as your patch did. Just stick to > plain text discussion in the email body if you aren't going to write a > patch in the sgml source language and present it as a diff. > > David J. > >
