Just posting a few further links with interesting (imho) reading.

In distributed databases the terms ACID and CAP invariably come up, eg. Stonebreaker claiming that both VoltDB and NimbusDB go for CA rather than AP:
http://cacm.acm.org/blogs/blog-cacm/83396-errors-in-database-systems-eventual-consistency-and-the-cap-theorem/fulltext

The term "Consistent" as used in ACID does not have the same definition as in the CAP theorem:
http://www.julianbrowne.com/article/viewer/brewers-cap-theorem
Actually, the "consistency" of the CAP theorem is more like the "atomicity" of ACID.

What Jim argued in this list recently, and Guy Pardon before him and Bettina Kemme before that, is that CAP only holds true within the strict assumptions of the proof:
http://guysblogspot.blogspot.com/2008/09/cap-solution-proving-brewer-wrong.html
The key is in the view of time and the realization that time is a very relative thing in distributed systems (google for "lamport clock 1978").

Using this understanding of relative time, NimbusDB is actually both ACID and CAP (and so are several other solutions).

Paul