The CAP theorem states that a distributed system cannot simultaneously be consistent, available, and partition tolerant

No distributed system is safe from network failures, thus network partitioning generally has to be tolerated.^[7][8] In the presence of a partition, one is then left with two options: consistency or availability.

CAP is often misunderstood as a choice at all times of which one of the three guarantees to abandon. In fact, the choice is between consistency and availability only when a network partition or failure happens. When there is no network failure, both availability and consistency can be satisfied.^[9][10] SQL relational databases such as CockroachDB, LeanXcale, NuoDB, or Google Spanner are counter-examples of this fallacy.

CAP has been used by many NoSQL database vendors as a justification for not providing transactional ACID consistency, claiming that the CAP theorem “proves” that it is impossible to provide scalability and ACID consistency at the same time. However, a closer look at the CAP theorem and, in particular, the formalisation by Gilbert & Lynch, reveals that the CAP theorem does not refer at all to scalability, but only availability (the A in CAP).^[11]

Database systems designed with traditional ACID guarantees in mind such as RDBMS choose consistency over availability, whereas systems designed around the BASE philosophy, common in the NoSQL movement for example, choose availability over consistency.^[5]

The PACELC theorem builds on CAP by stating that even in the absence of partitioning, there is another trade-off between latency and consistency.

A good easy to understand CAP proof

https://mwhittaker.github.io/blog/an_illustrated_proof_of_the_cap_theorem/

Consistency:

any read operation that begins after a write operation completes must return that value, or the result of a later write operation

Availability

every request received by a non-failing node in the system must result in a response

Partition Tolerance

the network will be allowed to lose arbitrarily many messages sent from one node to another

each CAP is hard to achieve!

Documents

https://en.wikipedia.org/wiki/CAP_theorem

https://www.youtube.com/watch?v=LW8MBYU_pzQ&ab_channel=NoSQLmattersConference