3 ms·

I think you are wrong about this, see my sibling comment. Postgres solves this by using MVCC. If you were right, there would be tons of example code retrying d

I think you are wrong about this, see my sibling comment. Postgres solves this by using MVCC. If you were right, there would be tons of example code retrying database transactions. In popular webframeworks, in Wordpress, Joomla, etc. And it is just not there.

dwohnitmok 6y ago
From https://www.postgresql.org/docs/12/transaction-iso.html https://www.postgresql.org/docs/12/transaction-iso.html For the Repeatable Read isolation level: > Applications using this level must be prepared to retry transactions due to serialization failures. For the Serializable isolation level: > However, like the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures. I haven't looked into the codebases of Wordpress or Joomla to figure out how they do things, but I've certainly written code myself to listen and retry for these Postgres exceptions (and that code does occasionally fire). That beings said these errors are fairly rare in practice because of how fine-grained Postgres's concurrency is and depend pretty heavily on your access patterns. It's possible for many production applications with certain access patterns and without too much load to go almost their entire lifetime without seeing these errors (or only seeing them when their users do something pathological). MVCC ensures that concurrent reading isn't blocked by both reading and writing (not both concurrent reading and writing) and that retrying is fine. If you concurrently read and write to the same piece of data MVCC can't help you without some sort of retry (because that would require basically omniscience in figuring out what the proper merge strategy is).
- the-dude 6y ago
  That is because nobody is running in these isolation levels. The only time I have dabbled with those levels was with a work queue and concurrent workers competing for the work ( rows ).
  - dwohnitmok 6y ago
    * shrugs * I mean I know teams that have run in those isolation levels for production apps but sure you generally don't need it if your queries aren't particularly complex (and indeed they are higher than the default level set by Postgres). But then you really are "papering over" the problem as gwd says and the Sqlite transaction is giving you higher consistency guarantees than the Postgres transaction (in particular successive SELECT statements in your Postgres transaction are not guaranteed to be consistent with one another).
  - dragonwriter 6y ago
    > That is because nobody is running in these isolation levels. Any isolation level below Serializable isn’t fully ACID. (Particularly, its not “I”.)
gwd 6y ago
> If you were right, there would be tons of example code retrying database transactions. In popular webframeworks, in Wordpress, Joomla, etc. And it is just not there. This is one thing that seemed really strange to me when I was learning this -- all the guides talk about (say) parameterized queries, and people mention transactions, but nobody says what to do if there's a conflict. I basically had to work it out myself. I'm not a DB person, but I am an Operating Systems person, and I am absolutely confident that there is no clever system that can work around the scenario I described above without 1) restarting one of the transactions, 2) failing one of the transactions, or 3) putting the DB in an inconsistent state. All I can think of is that in the vast majority of cases, people are getting lucky: It just happens that people almost never modify the same data at the same time; and if one out of every million operations fail, but do so in a safe manner (not corrupting data), people just chalk it up to your website being quirky or something. EDIT OK, I followed the link of the sibling, and read up about the SQL standard's various levels of transaction isolation. I'm not sure what my example would do in the "Read Committed" case (apparently the default for Posgresql); and I can't imagine programming anything important when I didn't actually know how the default consistency guarantee would behave.

dwohnitmok 6y ago

From https://www.postgresql.org/docs/12/transaction-iso.html https://www.postgresql.org/docs/12/transaction-iso.html For the Repeatable Read isolation level: > Applications using this level must be prepared to retry transactions due to serialization failures. For the Serializable isolation level: > However, like the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures. I haven't looked into the codebases of Wordpress or Joomla to figure out how they do things, but I've certainly written code myself to listen and retry for these Postgres exceptions (and that code does occasionally fire). That beings said these errors are fairly rare in practice because of how fine-grained Postgres's concurrency is and depend pretty heavily on your access patterns. It's possible for many production applications with certain access patterns and without too much load to go almost their entire lifetime without seeing these errors (or only seeing them when their users do something pathological). MVCC ensures that concurrent reading isn't blocked by both reading and writing (not both concurrent reading and writing) and that retrying is fine. If you concurrently read and write to the same piece of data MVCC can't help you without some sort of retry (because that would require basically omniscience in figuring out what the proper merge strategy is).

the-dude 6y ago
That is because nobody is running in these isolation levels. The only time I have dabbled with those levels was with a work queue and concurrent workers competing for the work ( rows ).
- dwohnitmok 6y ago
  * shrugs * I mean I know teams that have run in those isolation levels for production apps but sure you generally don't need it if your queries aren't particularly complex (and indeed they are higher than the default level set by Postgres). But then you really are "papering over" the problem as gwd says and the Sqlite transaction is giving you higher consistency guarantees than the Postgres transaction (in particular successive SELECT statements in your Postgres transaction are not guaranteed to be consistent with one another).
- dragonwriter 6y ago
  > That is because nobody is running in these isolation levels. Any isolation level below Serializable isn’t fully ACID. (Particularly, its not “I”.)

gwd 6y ago

> If you were right, there would be tons of example code retrying database transactions. In popular webframeworks, in Wordpress, Joomla, etc. And it is just not there. This is one thing that seemed really strange to me when I was learning this -- all the guides talk about (say) parameterized queries, and people mention transactions, but nobody says what to do if there's a conflict. I basically had to work it out myself. I'm not a DB person, but I am an Operating Systems person, and I am absolutely confident that there is no clever system that can work around the scenario I described above without 1) restarting one of the transactions, 2) failing one of the transactions, or 3) putting the DB in an inconsistent state. All I can think of is that in the vast majority of cases, people are getting lucky: It just happens that people almost never modify the same data at the same time; and if one out of every million operations fail, but do so in a safe manner (not corrupting data), people just chalk it up to your website being quirky or something. EDIT OK, I followed the link of the sibling, and read up about the SQL standard's various levels of transaction isolation. I'm not sure what my example would do in the "Read Committed" case (apparently the default for Posgresql); and I can't imagine programming anything important when I didn't actually know how the default consistency guarantee would behave.