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!
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.
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?
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.
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.
> 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.
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.
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.
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.
The Serializeable isolation level is used for data moves within the cluster according to the table referencing isolation levels and row visibility rules.
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.
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.
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).
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?
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.
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?
54 comments
[ 2.8 ms ] story [ 122 ms ] threadOn 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.
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?
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.
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.
'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.
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
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.
Schema transactions are not nearly as useful/important as data transactions.
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.
This has a way of biting one in the ass in the worst possible moment, during a migration.
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.
--
[1] http://stackoverflow.com/questions/4692690/is-it-possible-to...
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.
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.
Can you give an example?
The Serializeable isolation level is used for data moves within the cluster according to the table referencing isolation levels and row visibility rules.
Unfortunately the article is not a complete overview of the existing DBs landscape and has too much marketing in other places as well.
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.
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).
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.