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
In the meantime, hundreds of thousands of businesses are built on REPEATABLE READ.
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.
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.
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.
For simple CRUD applications SERIALIZABLE is great, because you basically don't have to worry about concurrent updates anymore. Well worth the price imho.
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.