4 ms·

Nitpick: while the gist is correct, the 2nd diagram illustrating how transactions don't help is subtly misleading. The diagram implies that time flows down in t

Nitpick: while the gist is correct, the 2nd diagram illustrating how transactions don't help is subtly misleading. The diagram implies that time flows down in the diagram. If so, then the update on the right won't occur until after the commit on the left. The update on the left really does acquire "write" lock on the balance, so the 2nd update on the right cannot occur until after the lock on the left is released when the left commits. That said, the effect is exactly as the author claims. The transaction on the right will ignore the change made by the one on the left.

kornish 10y ago
While time is down, it's the time of a hypothetical user entering the command, not the time at which commands actually affect data in the table. I think that's pretty intuitively clear to the reader, especially since the article is about counterintuitive behavior.
- quizotic 10y ago
  The point is that if a user on the right entered the update command, it would NOT return until after the transaction on the left committed, because there's a database lock on the balance when the left transaction did its update.
david-given 10y ago
That was the section I stalled at --- the author doesn't explain why the transactions don't help, just states that they don't, and provides a diagram with no context. I don't understand what they're getting at here. Surely as soon as one transaction tries to modify the database, it'll lock out all other reads to the affected rows from other transactions, so as to prevent them observing changed data until the transaction's committed? So avoiding the entire problem? Isn't this the entire reason transactions exist? Edit: I haven't actually used PostgreSQL; I grew up on SQLite. I see from the docs that SQLite transactions are all serializable, which from the linked article seems to be what you get with PostgreSQL's BEGIN ISOLATION LEVEL SERIALIZABLE. So... if by default PostreSQL's transactions aren't like this, what are they? If they're not serializable, then aren't they simply broken, i.e. not enforcing the transactions properly?

kornish 10y ago

While time is down, it's the time of a hypothetical user entering the command, not the time at which commands actually affect data in the table. I think that's pretty intuitively clear to the reader, especially since the article is about counterintuitive behavior.

quizotic 10y ago
The point is that if a user on the right entered the update command, it would NOT return until after the transaction on the left committed, because there's a database lock on the balance when the left transaction did its update.

david-given 10y ago

That was the section I stalled at --- the author doesn't explain why the transactions don't help, just states that they don't, and provides a diagram with no context. I don't understand what they're getting at here. Surely as soon as one transaction tries to modify the database, it'll lock out all other reads to the affected rows from other transactions, so as to prevent them observing changed data until the transaction's committed? So avoiding the entire problem? Isn't this the entire reason transactions exist? Edit: I haven't actually used PostgreSQL; I grew up on SQLite. I see from the docs that SQLite transactions are all serializable, which from the linked article seems to be what you get with PostgreSQL's BEGIN ISOLATION LEVEL SERIALIZABLE. So... if by default PostreSQL's transactions aren't like this, what are they? If they're not serializable, then aren't they simply broken, i.e. not enforcing the transactions properly?