5 ms·

No your intuition is right. At its highest levels of consistency settings Postgres will also throw an exception which application code is supposed to listen for

No your intuition is right. At its highest levels of consistency settings Postgres will also throw an exception which application code is supposed to listen for and retry the transaction as needed. This is functionally the same as what you're doing with Sqlite. The main difference is that this can happen more often for Sqlite because it has far coarser write-locking (where only one writer can proceed globally at a time) whereas Postgres is much finer so in practice this is less of an issue (that is B has to be the same database row in Postgres whereas B could be totally different bits of data in Sqlite). Postgres can also relax various consistency guarantees if they're not required, but that's effectively "papering over the problem" as you say. Although I would say that the presence of these exceptions isn't necessary a concurrency bug. Just automatically retrying the transaction is usually perfectly fine from a correctness point of view (this is optimistic concurrency vs pessimistically locking things). The only reason Postgres doesn't do that for you automatically is that done blindly you can waste a lot of performance on retries and the application may want to control what it does there to minimize performance impact (aborting after n tries, exponential backoff, etc.).

the-dude 6y ago
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.

the-dude 6y ago

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.