5 ms·
I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the ne
by dack 11y ago
I think in general when writing applications, you should assume that any data you keep in memory between SQL queries could become stale and change before the next query/update.
Anytime you have to update multiple individual records and rely on calculating state from both of them at once, alarm bells should start going off. Yes, you can start dropping into special transactions but there's also a potential opportunity for a better design.
Unfortunately, all that feels like a huge accidental complexity.
- acveilleux 11y agoTo some extent, isn't that the whole point of specifying the transaction isolation level you need? So you can make these assumptions?
- dack 11y agoSure, but there are tradeoffs in performance then. I'm not saying transactions are bad (although maybe my strong wording in the parent implies that), just that, especially if you're using an ORM, you shouldn't make those assumptions by default.
- rcthompson 11y agoThe only assumption that was made is that Galera cluster's SNAPSHOT ISOLATION actually implements SNAPSHOT ISOLATION. How is this not an assumption that should be made by default? If the database user has specified a specific isolation level, then they have already selected a specific trade-off in performance vs consistency. Giving them a weaker isolation level in the name of performance is like giving someone a car when they asked for a train ticket.
- bboreham 11y ago> Yes, you can start dropping into special transactions Transactions aren't "special" in SQL. You expect that reads and writes within a transaction are kept consistent, unless you have deliberately chosen a weaker serialization level.
- AlisdairO 11y agoIt's pretty common for RDBMSs to default to read committed, which is arguably a mistake on the implementer's part, but does allow for some pretty serious inconsistency if you don't know what you're doing.
- dack 11y agoWell, SERIALIZABLE isn't the default in Oracle, Postgres, or MySQL.. it's READ_COMMITTED, READ_COMMITTED, and REPEATABLE_READ respectively (unless the documentation i just looked up is out-of-date) I'm just saying you still can't rely on it by default, and have to start reading the details of the isolation levels. If you're doing that all the time, it might be a reason to rethink the design (however, there are of course some exceptions)