Concurrency — Many Hands on One Table

Everything so far assumed one thread. Real databases serve many connections at once — and the moment two threads touch the same table, code that has worked perfectly all course becomes a lottery. This lesson is about why it breaks and the discipline that fixes it.

What Actually Goes Wrong

Consider two threads both running our insert. It ends with:

t->rows[t->count].id = t->next_id++;
return t->rows[t->count++].id;

t->count++ looks atomic. It isn't — it compiles to load, add, store. Interleave two threads:

Thread A: load count (=5)
Thread B: load count (=5)
Thread A: store 6, write row into slot 5
Thread B: store 6, write row into slot 5   ← overwrites A's row!

Two inserts, one row, count is 6 but slot 6 was never written — it's uninitialized garbage that a later scan will happily serve as data. This is a race condition, and it has the worst possible debugging profile: it's timing-dependent, so it passes every test on your laptop and fires under production load; adding printf changes the timing and makes it vanish (a "heisenbug").

It gets worse. Our table reallocs when it grows. If thread A's insert triggers realloc — which may move the whole array and free the old one — while thread B is mid-scan holding a pointer into the old array, thread B is now reading freed memory. And even without realloc, a reader can see a torn row: writer has copied the name but not yet the age. There is no "mostly safe" here: the C memory model says a data race is undefined behavior, full stop.

Critical Sections and Mutexes

The fix is mutual exclusion: mark the read-modify-write sequences that must never interleave (critical sections) and let only one thread inside at a time. POSIX gives us the mutex:

pthread_mutex_lock(&t->lock);     /* blocks until the lock is free       */
/* ... critical section: this thread has exclusive access ... */
pthread_mutex_unlock(&t->lock);   /* next waiting thread may now enter   */

The rules that make mutexes work in practice:

• The whole invariant, not the hot line. The critical section must cover the entire sequence that takes the table from one valid state to another — grow-check, realloc, row copy, id assignment, count bump. Locking just count++ still lets a reader see the row half-copied.
• Every path unlocks. Including early returns on allocation failure. A returned-without-unlock mutex deadlocks the next caller forever. (This is the C version of why other languages have defer/RAII.)
• Never return pointers into locked state. If table_find_by_id returns &t->rows[i] and then unlocks, the caller reads that pointer outside the lock — racing every future insert's realloc. The safe pattern is copy out: the lookup copies the row into a caller-provided struct while holding the lock. This is why the challenge below has a table_find_copy(t, id, &out) signature, and it's the same copies-vs-references tension you met in the querying lesson — concurrency turns "slightly risky" into "undefined behavior".

Granularity: One Big Lock, and Why That's Respectable

We'll protect the whole table with a single mutex — every operation takes it. Simple to reason about, obviously correct, and it serializes everything: two CPU-heavy queries can't run simultaneously. The alternatives are a ladder of complexity you climb only when profiling says you must:

• Readers-writer locks (pthread_rwlock_t): many concurrent readers OR one writer. Great when reads dominate.
• Fine-grained locking: lock per page/row-range so writers on different pages proceed in parallel. Now deadlock becomes possible — thread A holds lock 1 wanting 2, thread B holds 2 wanting 1 — and the classic cure is a global lock ordering (always acquire in address/page-number order).
• MVCC: writers create new row versions instead of mutating, so readers never block at all — Postgres's approach, and SQLite's WAL mode for readers.

For perspective: SQLite itself runs one big lock. A single writer at a time, enforced with file locks; WAL mode relaxes readers, not writers. It handles enormous workloads that way. "One big lock, correctly" beats "clever locks, incorrectly" every time — you can always optimize a correct program.