Transactions, CAP and Scaling

In the last scriptum (Information_System_Architectures) we saw that every architecture is a trade-off between consistency, scalability and latency. This scriptum answers three questions:

How does a database keep data correct? → transaction models (ACID vs. BASE)
What happens when many users work at the same time? → isolation levels
What happens when the database is spread over many machines? → CAP theorem and scaling

1. What is a transaction?

A transaction is a group of operations that belongs together. Either all of them happen, or none of them.

Classic example — a bank transfer of 100 € from Anna to Ben:

START TRANSACTION;
UPDATE account SET balance = balance - 100 
WHERE owner = 'Anna';
UPDATE account SET balance = balance + 100 
WHERE owner = 'Ben';
COMMIT;      -- or ROLLBACK; if something went wrong

If the server crashes after the first UPDATE, the money must not simply vanish.

2. ACID — the strict model

Used by classic relational databases (MySQL/InnoDB, PostgreSQL, SQL Server, SQLite).

Letter	Property	In one sentence	Bank example
A	Atomicity	All or nothing.	Never only one of the two `UPDATE`s.
C	Consistency	A transaction moves the DB from one valid state to another valid state.	Constraints hold, e.g. `balance >= 0`.
I	Isolation	Parallel transactions do not disturb each other.	A report never sees “money in flight”.
D	Durability	After `COMMIT` the data survives crashes.	Power failure → transfer is still there.

Careful: The “C” in ACID (valid state, constraints) is not the same as the “C” in CAP (all nodes show the same data). Same letter, different meaning.

3. BASE — the relaxed model

Used by many distributed NoSQL systems (Cassandra, DynamoDB, CouchDB, …) where ACID across many nodes would be too slow.

Letter	Meaning	In plain words
BA	Basically Available	The system always answers — maybe with slightly old data.
S	Soft state	The state can change even without new writes (copies sync in the background).
E	Eventual consistency	If no new writes come in, all copies will eventually be equal.

Real-life analogy: a “like” counter on social media. If you see 1,203 likes and your friend sees 1,205 for a few seconds — nobody cares. A bank balance is different.

3.1 ACID vs. BASE at a glance

	ACID	BASE
Priority	correctness	availability & speed
Consistency	strong (immediately)	eventual (after a while)
Scaling	mostly vertical	horizontal, very good
Typical systems	MySQL, PostgreSQL, SQL Server	Cassandra, DynamoDB, CouchDB
Typical use	banking, orders, stock levels	social feeds, shopping carts, logs, IoT sensor data

It is not black and white: MongoDB has supported multi-document ACID transactions since version 4.0, and many NoSQL systems let you choose the consistency level per query.

4. Isolation levels

Perfect isolation (every transaction behaves as if it were alone) is expensive — it needs many locks and makes users wait. So the SQL standard defines four levels: the lower the level, the faster, but the more errors (anomalies) can happen.

4.1 The anomalies

Dirty Read — you read data that another transaction has not committed yet.

T1: UPDATE price = 50          (not committed)
T2: SELECT price  → 50         ← reads "dirty" data
T1: ROLLBACK                   → price was never really 50

Non-Repeatable Read — you read the same row twice and get different values.

T1: SELECT price → 40
T2: UPDATE price = 50; COMMIT
T1: SELECT price → 50       ← same query, different result

Phantom Read — you run the same query twice and new rows appear (or disappear).

T1: SELECT COUNT(*) FROM orders WHERE day = today → 10
T2: INSERT INTO orders ...; COMMIT
T1: SELECT COUNT(*) ...       → 11   ← a "phantom" row

4.2 The four levels

Isolation level	Dirty Read	Non-Repeatable Read	Phantom Read	Speed
Read Uncommitted	possible	possible	possible	fastest
Read Committed	✗	possible	possible
Repeatable Read	✗	✗	possible
Serializable	✗	✗	✗	slowest

Memory trick: every level going down removes one more anomaly.

Defaults:

MySQL (InnoDB): Repeatable Read
PostgreSQL, SQL Server, Oracle: Read Committed

4.3 How to set it

MySQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
  -- ...
COMMIT;

C# / ADO.NET:

using var tx = connection.BeginTransaction(
  IsolationLevel.Serializable);
// ... commands with tx ...
tx.Commit();

4.4 Bonus: the Lost Update

Two users read the stock (10), both subtract 1 and write back 9. Correct would be 8. One update is lost.

Solutions:

Let the DB calculate: UPDATE item SET stock = stock - 1 WHERE id = 7;
Pessimistic locking: SELECT ... FOR UPDATE locks the row until commit.
Optimistic locking: store a version number and update only WHERE version = @oldVersion (Entity Framework: concurrency token).

5. The CAP theorem

As soon as data is stored on several nodes, the network between them can fail. The CAP theorem says what you can still guarantee then.

Letter	Property	Meaning
C	Consistency	Every read returns the latest write — all nodes show the same data.
A	Availability	Every request gets an answer (no error, no timeout).
P	Partition tolerance	The system keeps working although the network between nodes is broken.

CAP theorem: When a network partition happens, a distributed system must choose between Consistency and Availability.

5.1 Why you cannot have all three

    Node A  ───── X ─────  Node B        (network cable cut)
   price=50               price=40       (B has not received the update)

A user asks Node B for the price. Node B has two options:

  1) answer "40"          → available, but NOT consistent  (AP)
  2) refuse / wait        → consistent, but NOT available  (CP)

There is no third option. And because networks always fail sometimes, P is not optional in a real distributed system — the real choice is C or A.

6. CAP classification

Class	Gives up	Behaviour during a partition	Examples
CP	Availability	Rather return an error than wrong data.	MongoDB (default: writes to the primary), HBase, etcd, ZooKeeper
AP	Consistency	Always answer, sync later (eventual consistency).	Cassandra, DynamoDB, CouchDB, DNS
CA	Partition tolerance	Only possible if there is no network between nodes.	a single MySQL / PostgreSQL server

               C
              / \
        CP   /   \   CA  (only single-node
            /     \       systems)
           P ----- A
              AP

How to choose:

Money, stock, reservations, anything where “wrong” is worse than “not now” → CP
Feeds, likes, carts, sensor data, where “now” is more important than “exact” → AP

Many modern databases are tunable: in Cassandra you can request QUORUM per query, in MongoDB you can read from secondaries. So “MongoDB is CP” describes the default, not a law of nature.

7. Scaling dimensions

7.1 Up or out?

Direction	What you do	+ / –
Vertical (scale up)	bigger machine: more CPU, RAM, SSD	+ simple, ACID stays easy · – hard limit, expensive, single point of failure
Horizontal (scale out)	more machines	+ nearly unlimited, fault tolerant · – distribution → CAP problems

7.2 The Scale Cube (three axes of horizontal scaling)

              Y: split by function
              (microservices)
                 ▲
                 │
                 │
                 └────────► X: clone the same thing
                /           (replicas behind a load balancer)
               /
              ▼
     Z: split by data
     (sharding)

Axis	Idea	Example	Scales
X	identical copies	5 Web-API instances behind a load balancer; DB read replicas	reads, requests
Y	split by function	separate services for users, orders, payments, each with its own DB	team size, features
Z	split by data	customers A–M on shard 1, N–Z on shard 2 (MongoDB sharded cluster)	writes, data volume

7.3 Scaling and transactions — the connection

Vertical / single server: ACID is easy, everything is local.
X-axis replicas: reads scale; with async replication replicas can be stale → eventual consistency.
Y- and Z-axis: a transaction may touch several databases → needs 2-Phase-Commit (slow, blocking) or you accept BASE.

The more you scale out, the more you move from ACID towards BASE and from CA towards a conscious CP or AP decision.

8. Summary

ACID = strict and correct, BASE = available and scalable, eventually correct.
Isolation levels trade speed for protection against Dirty, Non-Repeatable and Phantom Reads. Serializable is safest, Read Uncommitted is fastest.
CAP: in a network partition you must choose C or A. P is not optional.
CP for money, AP for likes. CA only exists on a single node.
Scale up first (simple), scale out when you must: X = clone, Y = split functions, Z = split data.

9. Self-check questions

Explain the four ACID properties using an online shop order.
What is the difference between the “C” in ACID and the “C” in CAP?
What does “eventual consistency” mean? Give an example where it is acceptable and one where it is not.
Which anomaly is described here: “I counted 10 orders, ran the same query again and got 11.”?
Which isolation level prevents dirty reads but still allows non-repeatable reads?
What is the default isolation level of MySQL InnoDB?
Two clerks sell the last ticket at the same time. Name the problem and two solutions.
Why is “CA” not a real option for a distributed database?
Classify a ticket booking system and a sensor-data logging system as CP or AP. Justify.
Name the three axes of the Scale Cube with one example each.
Why does sharding make transactions harder?

Short answers

A: order row and stock reduction both happen or neither. C: stock never negative. I: another customer doesn’t see the half-finished order. D: after “order confirmed” the order survives a crash.
ACID-C = rules/constraints are respected. CAP-C = all nodes return the latest value.
Copies become equal after some time without new writes. OK: like counter. Not OK: account balance.
Phantom read.
Read Committed.
Repeatable Read.
Lost update (or double booking). Solutions: SELECT ... FOR UPDATE, optimistic locking with a version column, or an atomic UPDATE ... SET stock = stock - 1 WHERE stock > 0.
Networks between nodes always fail sometimes, so the system must tolerate partitions. CA only works without a network — i.e. on one node.
Ticket booking → CP (selling a seat twice is worse than a short error). Sensor logging → AP (losing availability means losing data; a few seconds of inconsistency don’t matter).
X: replicas behind a load balancer. Y: microservices (orders, users, payments). Z: sharding by customer.
A transaction may need rows on several shards → distributed transaction (2PC) which is slow and blocking, or you give up ACID.

Deep Thought

Explorer

Transactions_CAP_Scaling