3 ms·

No, databases that support multiple connections "properly" make sure that the connections are isolated, a connection only sees committed data and can run any va

by dvdkon 6y ago

No, databases that support multiple connections "properly" make sure that the connections are isolated, a connection only sees committed data and can run any valid transaction at any time. It's actually a large part of database complexity, dealing with concurrent access performantly and correctly. The same thing would happen with PostgreSQL if you used a single connection in multiple threads.

gwd 6y ago
As I said, I'm not a DB expert, but "any valid transaction at any time" is clearly impossible. Suppose you have two transactions T1 and T2. T1 reads records A and B and then writes record B; T2 reads records A and B and writes record A. Then suppose they're interleaved as follows: T1: Read A, B T2: Read A, B --- Point X --- ? Once point X has been reached, there is no way to order the subsequent writes such that transactional consistency can be maintained: one way or another, one of those sets of reads must be re-issued. Maybe a good DB can detect simple cases like this and make sure they don't happen; but in the general case, the only way to make that happen is to restart the whole transaction. So for the first post in this thread, is SQLite is causing concurrency issues because it fails a second write transaction immediately, or is it exposing concurrency issues already in the code, which is papered over by Postgres managing to avoid conflicts in many situations?
- the-dude 6y ago
  Postgres always presents a consistent state and in particular does this with MVCC [0] It is SQLite which papers over concurrency in favor of simplicity ( the Lite part of the name is no coincidence ). [0] https://en.wikipedia.org/wiki/Multiversion_concurrency_control https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...
- dwohnitmok 6y ago
  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).

by dvdkon 6y ago

gwd 6y ago

As I said, I'm not a DB expert, but "any valid transaction at any time" is clearly impossible. Suppose you have two transactions T1 and T2. T1 reads records A and B and then writes record B; T2 reads records A and B and writes record A. Then suppose they're interleaved as follows: T1: Read A, B T2: Read A, B --- Point X --- ? Once point X has been reached, there is no way to order the subsequent writes such that transactional consistency can be maintained: one way or another, one of those sets of reads must be re-issued. Maybe a good DB can detect simple cases like this and make sure they don't happen; but in the general case, the only way to make that happen is to restart the whole transaction. So for the first post in this thread, is SQLite is causing concurrency issues because it fails a second write transaction immediately, or is it exposing concurrency issues already in the code, which is papered over by Postgres managing to avoid conflicts in many situations?

the-dude 6y ago
Postgres always presents a consistent state and in particular does this with MVCC [0] It is SQLite which papers over concurrency in favor of simplicity ( the Lite part of the name is no coincidence ). [0] https://en.wikipedia.org/wiki/Multiversion_concurrency_control https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...
dwohnitmok 6y ago
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).