Hacker News Reader

Comment by jwr | Hacker News Reader

Good article. Too many people just keep using the "ACID" term without really understanding what their transactional semantics are.

A good example of bumps you might hit is a problem I encountered a while ago: the default level of transaction isolation in postgres is “Read Committed”, not “Serializable” as the SQL standard mandates. This sounds fine, until you get constraint violations under load because of race conditions. See http://jan.rychter.com/enblog/2009/6/6/postgresql-transactio... if you want the full story.

BTW, I suspect problems like the one I encountered exist in lots of software systems, they just rarely get discovered.

View on HN

The VAST majority of database transactions operate correctly in READ COMMITTED mode. A vanishingly small amount need REPEATABLE READ, and even fewer need SERIALIZABLE, which, in reality, is not actually truly serializable in any MVCC database. The transaction isolation semantics in SQL were created before MVCC databases took hold and generally only roughly represent their intentions in an MVCC implementation.

Do any databases allow you to specify that a transaction needs a particular mode, and then that mode is implemented only for that transaction? e.g.,

  START TRANSACTION MODE READ REPEATABLE;

or something like that. It would seem to be a way to only use the extra resources when consistency is paramount. Of course it would take a lot of careful thought to determine which transactions need this mode...

PostgreSQL can do that: http://www.postgresql.org/docs/9.0/static/sql-set-transactio...

Given that we have no way of reliably determining which systems require higher levels of serialization for correctness, I'm not sure we should draw much comfort from this.

MySQL/InnoDB defaults to REPEATABLE READ, I believe. We started running into locking/concurrency issues in this mode a long time ago and found that for our uses, 99.9% of the time READ COMMITTED would work just fine. We now use READ COMMITTED as the default and switch to REPEATABLE READ only when needed. We handle about 100x more traffic now than we did when we were forced to make the switch.

Reply on news.ycombinator.com