Comment by hmottestad | Hacker News Reader

Comment by hmottestad | Hacker News Reader

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.

If you professors could actaully solve that problem efficiently they'd be billionaires.

Toys 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

Well 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.

Isn't Postgres just that? Or are you saying it's not efficient?

Yeah, 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.

> 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.

In 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.

The 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.

Used 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.

MSSQL transactions are trule serializable by default. MySQL InnoDB supports it too but you have to change the transaction isolation mode.

I believe the default for SQL Server is actually read committed.

https://msdn.microsoft.com/en-us/library/ms175909.aspx

Sorry, I was wrong.

MSSQL is read committed by default according to their documentation.

Reply on news.ycombinator.com