SQL, deeper.

Transactions — the unit of "all or nothing"

A transaction is a group of SQL statements that either all succeed or all fail together. There is no middle.

Classic example — money transfer between accounts:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second UPDATE fails (network hiccup, constraint, anything), COMMIT never runs and a ROLLBACK reverses the first UPDATE. Money never disappears. Without transactions, that's exactly what could happen — debit one account and never credit the other.

ACID — what transactions guarantee

The four concurrency anomalies

When multiple transactions run at the same time, four things can go wrong. Each isolation level (next section) blocks some of them.

Isolation levels — your knob

SQL defines four isolation levels. Higher = more protection, but also more locking and slower throughput.

* Postgres' implementation prevents phantoms even at REPEATABLE READ. MySQL's InnoDB also prevents most phantoms via gap locks. The classical rule applies to ANSI SQL.

Defaults you'll meet in real life

• Postgres / Oracle / SQL Server default to READ COMMITTED
• MySQL InnoDB defaults to REPEATABLE READ

For most CRUD apps, the default is fine. Reach for SERIALIZABLE only when you have specific concurrency invariants to protect — and accept the throughput cost.

Pessimistic vs optimistic locking

How Spring's @Transactional uses all of this

You'll see this in Week 4 Day 26 in detail. Preview:

@Transactional(isolation = Isolation.READ_COMMITTED)
public void transferMoney(Long fromId, Long toId, BigDecimal amount) {
  Account from = accountRepo.findById(fromId).orElseThrow();
  Account to   = accountRepo.findById(toId).orElseThrow();
  from.debit(amount);
  to.credit(amount);
  accountRepo.save(from);
  accountRepo.save(to);
}

Spring opens a transaction before the method runs, commits if it returns normally, rolls back if it throws an unchecked exception. The isolation level passes straight through to the database. Spring isn't doing anything magical — it's calling BEGIN / COMMIT / ROLLBACK for you.

Cascades — what happens when a parent goes

You said you want clarity on cascades. Here it is.

When a row is referenced by a foreign key in another table, deleting it raises a question: what should happen to the dependent rows? SQL gives you several answers, declared on the foreign key:

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER REFERENCES users(id) ON DELETE CASCADE,
  total       NUMERIC(10,2)
);

-- DELETE FROM users WHERE id = 1;
-- → also deletes all orders WHERE user_id = 1

JPA cascades vs. database cascades — different things!

This trips up everyone. JPA has its own cascade attribute on @OneToMany / @ManyToOne. That governs what JPA does in Java when you save / remove an entity — separate from the database FK rule.

Two layers, two settings. We'll fully untangle this in Week 4 (JPA day). For now: they're not the same thing.

Normalization — the brief version

Database normalization is a design discipline that eliminates redundant data by splitting one fat table into many small related ones.

orders
---
id, user_email, user_name,
user_address, item_name,
price, qty

users (id, email, name, address)
items (id, name, price)
orders (id, user_id, item_id, qty)

The "forms" simplified

• 1NF — each cell is a single value (no comma-separated lists in columns)
• 2NF — every non-key column depends on the whole primary key
• 3NF — non-key columns depend only on the key, nothing else

For 95% of business apps, aiming for 3NF is the sweet spot. Higher forms (BCNF, 4NF) are for theoretical edge cases.

Deadlocks — when two transactions block each other

The fix is simple but surprising: always acquire locks in the same order across your codebase. If every code path locks "smaller id first," there can be no cycle.

End of Day 10. Tomorrow: Raw JDBC — one full hands-on day. Feel the boilerplate Spring saves you from.