Which modern databases support ACID transactions? | Hacker News Reader

Which modern databases support ACID transactions?

foundationdb.com

40 pointsspullara54 comments

> MySQL provides ACID transactions on a single machine

That made me chortle. If you can't roll back an `ALTER TABLE` command (e.g., to back out of a failed schema migration), you don't really have good ACID semantics. Here's a list of other fun MySQL commands that sidestep transactions: http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

Isn't ALTER TABLE a fundamentally slow and blocking action anyway? It's easy enough to make a new table and swap it in. Transactions for row-level actions are far more important.

The ability to do a schema migration consisting of several changes as an all-or-nothing, all-at-once change would be useful even if they can't figure out how to do it concurrently with other operations on the tables.

It shouldn't require too crazy of an implementation to allow reads concurrent with schema modification (seeing the old version of the world) and it's not impossible to allow concurrent, transactional writes too.

It happens to be a slow and blocking action on MySQL, yes. That's yet another problem with MySQL. You can only make a new table and swap it in if you can stop writing to your old table, or don't care if records go missing. I've lost weeks trying increasingly convoluted methods to get an online schema migration safely in MySQL, with no satisfying conclusion. You can sometimes get away with pt-online-schema-change's method of adding triggers to copy new rows while copying over the existing table contents, but that failed pretty badly for us because of collisions with the innodb gap lock on the table, so we ended up having to do multiple passes, populating the new table once without the live updates, then copying it over again while the live updates were copied over with triggers. It was a mess.

I think I expressed myself poorly. Undoubtedly it would be good if it did the on-disk data changes in the background, and a schema update seemed like an instant atomic action. But it's still a sequence point, and I see no way that transactions after it could complete while the schema update is uncommitted. So what do you actually gain by being able to do the schema update in a transaction?

The original comment was about being able to roll back, not the ability to run things in parallel. With PostgreSQL, I can start a transaction in which I'm going to perform five separate "alter table" commands; if one of these fails (maybe because I change the type of a field in a way that is incompatible with some of the data, or maybe because this is some kind of "migration" that came with a project and it has a bug: maybe it tries to rename a column, but there's a typo in the name) the entire migration will roll back atomically.

Yeah, I'm just saying that you can work around that pretty easily. There are more important things than rolling back schemas that mysql lacks. And if it got those things, schema updates would be pretty easy even without the ability to roll them back. Not extremely convenient, but convenient enough.

Schema transactions are not nearly as useful/important as data transactions.

The property you are looking for - that every SQL command can take place in a transaction - is surely a very desirable property for a SQL database to have, but from my perspective a database can provide genuine ACID transactions even if it only supports a subset of read and write operations within them.

Actually, I think we are giving too much credit to Oracle! There are (relatively subtle, but real) concurrency anomalies in their so-called "serializable" isolation level.

Not even just alter table, it's all DDL, I believe.

This has a way of biting one in the ass in the worst possible moment, during a migration.

MySQL isn't the only engine that does this.

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/implicit-c...

Edit - I was wrong!

Not only did I link to the wrong docs, but Oracle fixed what I considered to be a longstanding bug.

Thanks to those who pointed it out.

For a very, very long time, Oracle has enforced implicit transaction boundaries around DDL, and it is about time that finally changed.

Those docs look like they're MySQL docs, not Oracle Database docs. Stackoverflow suggests[1] that as-of Oracle Database 11g Release 2, DDL changes are isolated within the current transaction.

[1] http://stackoverflow.com/questions/4692690/is-it-possible-to...

That link is to a page from the MySQL 5.1 reference manual.

As of about 4 years ago Oracle's been the owners of MySQL, so that's the reason why you're seeing it hosted at docs.oracle.com.

ACID refers to actions that can take place within a transaction. Transactions refer to logical operation to the data. The data is the content, not de container. DML affects content, DDL affects the container. ALTER TABLE is a DDL operation.

Now, I know that _some_ databases offer some level of support to include DLL operations within a transaction, but its far from being common, and most of the implementations are quite recent.

Transactional DDL is a dangerous game with any DBMS. You must know the idioms of the systems you use, and use them accordingly. You would be a fool otherwise.

"Transactional DDL is a dangerous game with any DBMS."

Can you give an example?

I think that's exactly why we must have correct ACID transaction around DDL…

VoltDB uses a partitioned data model. For OLTP apps that fit within that model, it's incredibly fast and there's no global transaction. For transactions that span partitions, yes, there is a lock. If your app fits the model (and a lot of transaction processing does), then it's quite amazing. For instance, one model I've played with is updating customer balances in realtime. If I partition everything on CustomerId, then I can insert new charges and update balances at extremely high rates (over 50Ktps per node, at least).

If your app requires (edit: a significant amount of) transactions that have no locality, that is multiple arbitrary rows might be updated in any transaction, VoltDB might not be for you.

If I understand Spanner, it, too, has a somewhat-partitioned model, with parent/child relationships, correct?

Spanner can do partition-spanning transactions with a useful throughput.

Two things to point out as a VoltDB engineer.

1) VoltDB's global transactions are much slower than its partitioned transactions, but it's relative. It can still do hundreds or thousands per second.

2) Making this number faster for common cases is ongoing work. In upcoming 4.0, multi-partition consistent reads will get about a 10x performance boost if they're bottlenecked on transaction overhead. This allows us to do up to 50k/sec global reads while doing 500k/sec partitioned writes. Expect improvements for global writes in 2014.

I'm afraid there were more 'databases' created in last 2 years than in all earlier history of human civilization. This suggests authors of some of these products have yet to discover what ACID means and how many problems they didn't even think of have already been solved many years ago.

Making no mention of Microsoft SQL Server seems like a pretty big oversight.

Didn't you get the memo? By modern they meant not MS.

All the more peculiar, then, that they list RavenDB.

I'd never use Microsoft SQL Server since it doesn't run my preferred OS: Linux.

We use MySQL-Galera and it has full ACID guarantees in cluster/multi-site/DR environment.

Unfortunately the article is not a complete overview of the existing DBs landscape and has too much marketing in other places as well.

I know very little about Galera replication, but its FAQ states that it provides only snapshot isolation.

You are certainly correct that we have not provided a complete overview of the DB market! That is a very ambitious project. We built this page largely to call out the use of "ACID" terminology by vendors that don't actually provide it.

I personally think that "provides only snapshot isolation" and "provides ACID transactions on a single machine, but sharding, caching, and failover will likely violate them as you try to scale" are very different statements. The different isolation modes does not violate ACID transactions and only describe the semantic in more details. I would make a claim that majority of applications can use any isolation mode with ACID transactions as long as application developers understand what exactly going on.

Isolation levels describe the degree of compliance with the I in ACID. Full compliance is called serializable isolation. Are there valuable uses for lower degrees of isolation? Yes, and a good database should allow applications to selectively reduce isolation. But the inability to support serializable isolation greatly limits the power and composability of a system.

Galera looks like an extremely useful tool, and a big improvement on usual (and extremely dangerous) ad hoc failover. Besides the limited isolation level, though, it only really solves failover. Typical large scale database deployments will sacrifice even more ACID properties by sharding (which loses A, C, and I for shard-spanning transactions) and frequently by incoherent caching (which sacrifices isolation for reads that use the cache).

> RavenDB uses a weak form of isolation called "snapshot isolation". ... Index updates are not Atomic.

If MVCC (http://en.wikipedia.org/wiki/Multiversion_concurrency_contro...) is "weak", then are they claiming read/write locks are better?

Also, to nitpick, RavenDB's reads/writes in the document storage engine are entirely atomic. A lucene index is maintained in a secondary store which is eventually consistent. But as a document store supports ACID transactions.

As we disclaim on the page itself, we aren't experts on all of these databases, so it's possible that we've made a mistake. We are relying on the documentation of other products (but on the specific documented claims rather than whether they throw around the term "ACID").

'Snapshot isolation' is a level of isolation guarantee, not an implementation technique. It means that a transaction will read values consistently at one point in history and then write values at a later point, even though the read values may change in between. To give a classic example, pure snapshot isolation doesn't allow you to soundly transfer $100 from Alice's account to Bob's account. FoundationDB uses MVCC and optimistic concurrency, but provides serializable isolation.

Besides this, and the asynchronous indexing that you mention, RavenDB uses an "XA" type technique for cross-node transactions which relies on the durability of an external transaction coordinator. Various public statements of the developers lead me to think that they don't find this arrangement more trustworthy than I do.

"Local" transactions on a single document don't qualify as ACID transactions; that's one of the primary messages of the page you are linking to.

Worth mentioning (if it's not obvious) that this is written by the FoundationDb guys, on their site, and so will clearly show a bias toward their product (surprise!, it does!).

There is still some interesting information here, but there are some notable missing players here (MSSQL) who I presume are missing because not including them paints Foundation in a better light.

Where is Datomic?

And MSSQL?

Presumably SQL Server fits somewhere up at the top with MySQL/Postgres/Oracle?

Yep.

As far as we know, Datomic's (noninteractive) transactions are ACID. You would do better to ask them, though. The list isn't intended to be exhaustive!

The absence of Datomic seems a tad suspect.

From what I understand, Datomic sidesteps the ACID issue altogether by dealing with immutable data at the application layer. You add your changes on top of a version of the database, so you're always working on a consistent view of the database (pretty much like Git).

On the backend, Datomic doesn't implement it's own storage, it plugs into SQL databases, Riak, etc. so you may or may not have ACID at that level.

Google Cloud Datastore - https://developers.google.com/datastore/ and Google Cloud SQL https://developers.google.com/cloud-sql/faq

Firebird should be added to the ACID compliant list as well: http://www.firebirdsql.org/file/documentation/papers_present...

OrientDB (www.orientdb.org) Graph Database supports ACID transactions since the first version. It has SQL, Document Database features and it's distributed. Did FoundationDB inspire to OrientDB?

Am I wrong in thinking that [Neo4j](http://www.neo4j.org/) supports fully ACID compliant transactions?

By default, Neo4J is not ACID compliant as it works mainly in memory, which fails the Durability part of ACID. I am not event sure that if you would configure it to be in sync with its stored replica, it would pass ACID test due to how Java handle the files internally.

Fair enough - my experience with Neo is at this point shallow at best. I had seen it in the feature spec is all. Should they be claiming they are if they aren't however?

I thought PostgreSQL's replication was ACID-compliant?

It can, if configured accordingly. The article is missing a lot of point for other DB's too. Not a very good reference.

If you used synchronous replication. That's a hit for scalability. It's CAP all the way down if replication is asynchronous.

It would be nice to see a "NewSQL" database like Clustrix in this comparison. From my understanding of their white papers the transactions are ACID.

We have been told by people who should know that Clustrix does not provide serializable transactions, but since this claim is unspecific and not based on publically available information we haven't put it on the page.

Apparently as per their docs (http://docs.clustrix.com/plugins/viewsource/viewpagesrc.acti...) this is true specific to end-user transactions: "Note: serializable isolation not currently available to end user transactions."

The Serializeable isolation level is used for data moves within the cluster according to the table referencing isolation levels and row visibility rules.

What about RethinkDB?

From RethinkDB FAQ (http://www.rethinkdb.com/faq/) "RethinkDB is not a good choice if you need full ACID support or strong schema enforcement — in this case you are better off using a relational database such as MySQL or PostgreSQL."

heh, this applies to all 'new' databases - if you aren't sure which one to choose, you're usually better off staying with a relational. You'll be bleeding edge in few years when SQL is forgotten and then re-discovered.

Yes, but I was more thinking where was the mention -- while it's not particularly good for ACID properties I like rethinkdb, as a nosql DB

Reply on news.ycombinator.com