So I'd be interested in the HN hive-mind on this one... I've never done DB programming before a few years ago, and have basically only done it with SQLite. So when I ran into this sort of error, I just figured it was normal, and wrote code to check for the BUSY and LOCKED errors and just retried the whole transaction in a loop.
TFA also mentions `PRAGMA busy_timeout`, which looks like it would do something similar (but with a timeout).
Is that not normal for databases? Can't you get a transaction conflict which needs to be retried for Postgres as well?
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.
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?
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.).
> 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.
> 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).
* 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).
