CAP theorem and the concept of "Eventually Consistent" has gained prominence over the last few years. The extreme scalability requirements for massive social networking and content sites has been one of the main driving factors for this.
Brewer's CAP Theorem: There are three core system requirements for designing and deploying data centric applications in a distributed environment; Consistency (C), Availability (A), and Partition Tolerance (P). At a given time a distributed system can guarantee only two of them, an not all the three.
RDBMS vs Non-RDBMS:
In traditional RDBMS systems, transactional consistency is guaranteed by going for Consistency (C) and Availability (A) at the exclusion of Partition Tolerance (P).
In many non-RDBMS persistence mechanisms (particularly the highly scalable NoSQL variants like CouchDB, Voldemort, MongoDB, etc.), Availability (A) and Partition Tolerance (P) is guaranteed at the cost of Consistency (C).
ACID vs Eventually Consistent:
Traditional RDBMS systems can guarantee ACID (Atomicity, Consistency, Integrity, Durability), and in effect tries to ensure that the data is consistent at any given instant. But for most business systems instantaneous consistency is not a real requirement, and most of them can tolerate some time delay for data consistency.
The concept of being "Eventually Consistent", doesn't try to make the data instantaneously consistent but can guarantee that the data will be consistent eventually after a deterministic period of time.
This acceptable relaxation in consistency allows persistence systems like NoSQL to get the benefit of extreme scalability by going for A & P and relaxing the C in CAP.
Eric Brewer's Original Presentation can be found here.
A fairly detailed explanation of CAP by Julian Browne.