
 ACID in Theory and Practice - wglb
http://danweinreb.org/blog/acid-in-theory-and-practice
======
jwr
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...](http://jan.rychter.com/enblog/2009/6/6/postgresql-
transactions.html) 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.

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

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

~~~
hasanove
PostgreSQL can do that: [http://www.postgresql.org/docs/9.0/static/sql-set-
transactio...](http://www.postgresql.org/docs/9.0/static/sql-set-
transaction.html)

------
jhugg
Posted this on Dan's blog, but will add here too:

A counterpoint to the nobody really uses full isolation levels is VoltDB. Due
to it’s unique single-threaded architecture, it can ONLY perform transactions
with full serializability guarantees, and it does it with remarkable
performance. Our head of field engineering, Tim Callaghan, used to be a die-
hard Oracle user and he keeps saying how refereshing it is to actually not
have to think about concurrency.

~~~
benblack
It is a triumph of vendor marketing (Hi, John Hugg!) that reasonably informed
engineers think ACID == SQL/relational and that NoSQL == EC. Both are false.
As a couple of examples, have a look at Google Percolator
<http://research.google.com/pubs/pub36726.html> and Scalaris
<http://code.google.com/p/scalaris/> .

~~~
jhugg
I am John Hugg. I do work for VoltDB. I am proud of our transaction isolaton.

Not sure why this comment was a reply to me though. I've never claimed all
NoSQL is EC, not all SQL/Relational is ACID. I totally agree there is a lot of
unfortunate confusion, though I wouldn't limit the blame.

~~~
benblack
From the VoltDB FAQ (<http://community.voltdb.com/faq#id545596>):

Key-Value stores are a mechanism for storing arbitrary data (i.e. values)
based on individual keys. Distributing Key-Value stores is simple, since there
is only one key. However, there is no structure within the data store and no
transactional reliability provided by the system.

~~~
jhugg
Sorry. I didn't write it and I'll see what I can do about toning down the
language. In the defense of the text, while some Key-Value stores provide
atomic operations on individual values, I'm not aware of many that offer
actual transactions in the traditional sense of the term.

If you want to complain about our marketing, feel free to email Fred Holahan
(fholahan at voltdb.com). He didn't write everything on our website, but he's
in charge.

------
fauigerzigerk
What I have seen in the real world is that different parts of those guarantees
are used in different situations. ACID guarantees are often relaxed, as he
rightly says, but for some transactions or reports it is absolutely critical
to get it right and performance is secondary. A missed deadline or an error in
an important financial report can be an existential threat to a company.
That's why database technology is bound to be changing very very slowly for
mission critical tasks. It's not an academic issue.

------
VladRussian
"Oracle DB has more than one “isolation level”. The strongest, READ
REPEATABLE, provides ACID transactions."

i couldn't read his blabber past this point. Before talking about something
you don't know about, check the docs at least:

\---

[http://download.oracle.com/docs/cd/B28359_01/server.111/b283...](http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#i17856)

"Oracle Database offers the read committed and serializable isolation levels,
as well as a read-only mode that is not part of SQL92. Read committed is the
default."

\---

To make it clear:

1\. there is no READ REPEATABLE in Oracle

2\. in general, among the isolation levels, READ REPEATABLE isn't the
strongest

------
rbranson
Most people don't operate in a 100% ACID environment. However, This has a
pretty low impact on web applications, so many of us can breathe easy.
PostgreSQL's default isolation level is READ COMMITTED and InnoDB's default
isolation level is REPEATABLE READ. While this might seem like a big
difference, the SQL standard is pretty loose in the definition of these, and
because of their implementations, they have roughly equivalent consequences.
From what I understand, most InnoDB users that demand better performance in
high concurrency situations switch the default to READ COMMITTED.

