Part 4 of 8 · 3 min read
What joins and rules protect
Multi-row consistency, constraints, and a worked example of a transfer that goes wrong.
Think of a bank clerk moving cash between two drawers. If they take 100 out of drawer A and are called away before putting it in drawer B, 100 has vanished. A careful clerk makes the two moves one act: both happen, or neither.
Relational databases offer this as a transaction.
Transaction
A transaction groups several reads and writes into one unit that either happens completely or not at all, and that other users never see half-done.
Example: a transfer that subtracts 100 from account A and adds 100 to account B, as one unit.
The set of promises is often called ACID: atomicity (all or nothing), consistency (the rules always hold), isolation (overlapping transactions do not disturb each other) and durability (a finished write survives a crash). Level 5 explains each in detail. For now it is enough to know that relational databases normally provide these, and that many NoSQL stores offer fewer promises, or offer them only for a single record.
A worked example. Transfer 100 from account A (balance 500) to account B (balance 200).
With a transaction:
- Begin.
- A: 500 − 100 = 400.
- B: 200 + 100 = 300.
- Commit. If the server crashes after step 2, the database undoes step 2 on restart. A is 500, B is 200.
Without a transaction, on a store that guarantees only single-record writes:
- A is set to 400.
- The server crashes.
- B is still 200.
- Total money: 400 + 200 = 600, down from 700. One hundred has vanished.
The application can add safeguards, such as a log of intended steps that is replayed after a crash, but each safeguard is more code and more chances for bugs. For money, stock and booking, doing without transactions is rarely a good idea.
Two other protections come with tables.
- Constraints. The database refuses bad data: a foreign key must point to an existing row, an email must be unique, a price cannot be negative.
- Joins. Questions that cross tables are answered in one query, using the optimizer.
The book "Designing Data-Intensive Applications" says the same about joins. If the document database does not support them, the application must make several queries and combine the results itself, which moves complexity into the code and is usually slower. It warns that data tends to become more interconnected as features are added.
Eventual consistency
Eventual consistency means that if no new writes arrive, all copies of the data will in time show the same value, though for a while some copies may show an older one.
Example: after a user changes a profile photo, a friend in another region may see the old photo for a few seconds.
Many NoSQL systems choose this weaker promise in return for speed and easy spreading over many machines. For a like counter that is fine. For a bank balance it is not. Level 5 returns to this.
Common mistake
Picking a store without transactions for money and then discovering the gap in production. If the data has a rule that spans two records, ask how the rule will hold when a server dies halfway.
Check yourself:
- In the example without a transaction, how much money vanished, and why?
- What is eventual consistency, and for what kind of data is it acceptable?