6 ms·
Essentially, how to make a bank account in SQL. The author then explains how transactions won't help solve the problem, followed by an example with transactions
by hmottestad 10y ago
Essentially, how to make a bank account in SQL. The author then explains how transactions won't help solve the problem, followed by an example with transactions that actually does solve the case.
At university we learn that transactions should be serializable and that they are meant for these exact cases.
Transactions aren't actually true transactions unless they are serializable.
Most databases don't actually default to serializable transactions, but instead try to get close by implementing snapshot isolation. These aren't true transactions, since snapshot isolation doesn't truly isolate transactions.
The article had a bunch of nice ways to get better isolation than snapshot for a bunch of special cases. These are all very nice, since they can be useful for mainting performance together with integrity.
My professors at university still laugh at commercial databases and calls them toys for still not having implemented more efficient serializable transactions.
- calpaterson 10y agoWell if they know of a database that implements a workable subset of SQL and has efficient SERIALIZABLE transaction isolation we'd all love to hear about it. In the meantime, hundreds of thousands of businesses are built on REPEATABLE READ.
- icebraining 10y agoIsn't Postgres just that? Or are you saying it's not efficient?
- calpaterson 10y agoYeah, Postgres's implementation of SERIALIZABLE is not efficient (at least compared to the default). I'm not really sure how any implementation can be particularly efficient. It seems that either an implementation would have to maintain a huge number of specific row locks or you have to start locking whole tables.
- anarazel 10y ago> Yeah, Postgres's implementation of SERIALIZABLE is not efficient (at least compared to the default). Sure, you pay some price. But if that overhead actually proves to be a problem for you, we'd like to hear about it. There's definitely some room for optimizations, but so far fewer people hit those than some people (i.e. me) anticipated. > I'm not really sure how any implementation can be particularly efficient. It seems that either an implementation would have to maintain a huge number of specific row locks or you have to start locking whole tables. You don't need full row locks, and you can summarize up "ranges" of locks if necessary for space reasons. I.e. go from row level to page level, to full table level. That will still be correct, just cause more rollbacks.
- href 10y agoIn my personal use I found the overhead to be negligible. But then I've never used it for a project that really had to scale. For simple CRUD applications SERIALIZABLE is great, because you basically don't have to worry about concurrent updates anymore. Well worth the price imho.
- quizotic 10y agoThe efficiency challenge with serializable transactions comes mostly in the context of distributed transactions. Netezza/IBM had a reasonably efficient implementation. Instead of broadcasting individual locks, they shared ordering relations between transactions as they were discovered. Essentially, when transaction A reads a record and concurrent transaction B modifies the record, then B serializes after A (even if B's modification occurs before A's read in real time). These ordering relationships form a a graph. If a new ordering relationship between transactions causes the formation of a cycle, then some transaction must be aborted.
- yomly 10y agoToys which are propping up most of civilization then :) What is holding back more efficient serializable transactions becoming more mainstream? Is it: A lag between academia and industry? Entrenched software which makes it hard to implement / update to modern technology Performance issues which make the benefits for safer transactions not worth the computation tradeoff
- usloth_wandows 10y agoIf you professors could actaully solve that problem efficiently they'd be billionaires.
- barrkel 10y agoUsed throughout, serializable transactions will frequently deadlock, because the order of read operations needs to be globally consistent and the database doesn't have enough insight into the data dependencies and processing of query results by the application to reorder things.
- seunosewa 10y agoMSSQL transactions are trule serializable by default. MySQL InnoDB supports it too but you have to change the transaction isolation mode.
- wmfiv 10y agoI believe the default for SQL Server is actually read committed. https://msdn.microsoft.com/en-us/library/ms175909.aspx https://msdn.microsoft.com/en-us/library/ms175909.aspx
- seunosewa 10y agoSorry, I was wrong.
- hmottestad 10y agoMSSQL is read committed by default according to their documentation.