SQL & Transactions for Spring Developers

A relational database organizes data into tables whose rows share declared columns and types. The schema is an executable contract: primary keys establish identity, foreign keys preserve relationships, unique constraints prevent duplicates, checks reject invalid values, and nullability makes required data explicit. Application validation improves feedback, but database constraints protect the data from every writer, including scripts and future services.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL CHECK (length(trim(title)) > 0),
  status      text NOT NULL DEFAULT 'OPEN'
              CHECK (status IN ('OPEN', 'COMPLETED')),
  version     bigint NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);

CREATE TABLE task_events (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id     bigint NOT NULL REFERENCES tasks(id),
  event_type  text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

PostgreSQL is the canonical database for Module 0 and the later persistence path. timestamptz stores an instant and displays it using a session time zone. The event table references a valid task, while the status check prevents arbitrary strings. These rules exist before JPA; later annotations and migrations must represent the same database contract rather than invent a second one.

SELECT chooses columns and rows; WHERE filters before projection is returned; ORDER BY makes output order explicit. Without ordering, row order is not a contract. Parameterize values in application code instead of concatenating SQL, both for correctness and to prevent injection. During the lesson, literal values make the query visible, but production Java should bind parameters.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

INSERT INTO tasks(title)
VALUES ('Read Module 0')
RETURNING id, title, status, version;

SELECT id, title, status, created_at
FROM tasks
WHERE status = 'OPEN'
ORDER BY created_at DESC, id DESC
LIMIT 20;

UPDATE tasks
SET title = 'Finish Module 0',
    version = version + 1
WHERE id = 42
RETURNING *;

INSERT ... RETURNING retrieves database-generated values without a separate lookup. An UPDATE or DELETE should normally include a precise predicate; checking its affected-row count distinguishes “changed one row” from “matched nothing.” A missing predicate can modify an entire table, which is why the Watch exercise should make that risk observable inside a disposable database.

A foreign key stores the relationship between facts. A join reconstructs a result across those tables. The database does not automatically include related rows merely because a foreign key exists; the query chooses the join type and condition. An inner join keeps matching pairs, while a left join preserves every row from the left even when no event exists.

1
2
3
4
5
6
7
8
9
10

SELECT
  t.id,
  t.title,
  t.status,
  count(e.id) AS event_count,
  max(e.occurred_at) AS last_event_at
FROM tasks t
LEFT JOIN task_events e ON e.task_id = t.id
GROUP BY t.id, t.title, t.status
ORDER BY t.id;

Aggregation changes the result’s grain. The query above returns one row per task, so every selected non-aggregated column participates in the grouping. Before writing a join, state the desired grain in words: one row per task, one row per event, or one row per status. Many incorrect reports are grain mistakes rather than syntax mistakes.

An index is an additional data structure that can reduce the work required to find or order rows, at the cost of storage and extra work on writes. Primary keys and unique constraints normally create supporting indexes, but foreign-key columns and common filters may need deliberate indexes. “Add indexes everywhere” is not a strategy; inspect representative queries and data volume.

1
2
3
4
5
6
7
8
9
10

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM tasks
WHERE status = 'OPEN'
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX tasks_open_created_idx
  ON tasks (created_at DESC)
  WHERE status = 'OPEN';

EXPLAIN shows the planner’s chosen strategy; EXPLAIN ANALYZE actually executes the query and reports measured timing and row counts. Use it carefully for writes because the statement still executes unless you protect it with a rollback. Tiny tables often prefer a sequential scan even when an index exists, so interpret the estimate and observed rows instead of treating “Index Scan” as the only acceptable output.

Completing a task and recording its audit event form one business operation. If one statement commits without the other, the database tells two incompatible stories. A transaction groups the statements so commit makes all changes visible as a unit and rollback makes none of them durable. Atomicity does not mean concurrent work never conflicts; isolation controls which intermediate effects transactions can observe.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

BEGIN;

UPDATE tasks
SET status = 'COMPLETED',
    completed_at = now(),
    version = version + 1
WHERE id = 42
  AND status = 'OPEN'
  AND version = 3
RETURNING id, version;

-- Insert only after the UPDATE returned exactly one row.
INSERT INTO task_events(task_id, event_type)
VALUES (42, 'TASK_COMPLETED');

COMMIT;

The status and version predicates turn the update into a guarded transition. Zero affected rows can mean the task is missing, already complete, or stale; the application may issue a focused read to distinguish those public outcomes. If the event insert fails, rollback must undo the task update. Spring’s @Transactional will later define this boundary declaratively, but PostgreSQL still provides the guarantee.

Without explicit atomic guards or row locks, concurrent read-modify-write cycles silently destroy data via the Lost Update Anomaly.

1
2
3
4
5
6
7
8
9
10
11
12

-- Thread 1 reads task (version = 1)
SELECT id, title, version FROM tasks WHERE id = 42;

-- Thread 2 reads the same task concurrently (version = 1)
SELECT id, title, version FROM tasks WHERE id = 42;

-- Thread 1 modifies title and writes back:
UPDATE tasks SET title = 'Title A', version = 2 WHERE id = 42;

-- Thread 2 overwrites Thread 1 without checking version:
UPDATE tasks SET title = 'Title B', version = 2 WHERE id = 42;
-- Thread 1's write is silently lost forever!

To prevent this, production applications use Optimistic Locking (UPDATE tasks SET ... WHERE id = 42 AND version = 1). If the version has advanced, the update returns 0 affected rows, triggering a Spring Data OptimisticLockingFailureException and prompting a safe retry.

Now verify this in practice: open two psql sessions against the same disposable PostgreSQL database. In both sessions, begin a transaction and read the same open task. Let session A perform the guarded update but pause before commit. Run the same update in session B and observe its wait. After A commits, B rechecks the predicate and affects zero rows. The final status is valid and only one event should exist.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Session A
BEGIN;
UPDATE tasks
SET status = 'COMPLETED', version = version + 1
WHERE id = 42 AND status = 'OPEN' AND version = 0;
-- pause, then COMMIT

-- Session B, while A is paused
BEGIN;
UPDATE tasks
SET status = 'COMPLETED', version = version + 1
WHERE id = 42 AND status = 'OPEN' AND version = 0;
-- after A commits: UPDATE 0
ROLLBACK;

Session A: UPDATE 1, COMMIT\nSession B: UPDATE 0, ROLLBACK

PostgreSQL’s default Read Committed level begins each statement with a fresh command snapshot and rechecks an update predicate after waiting on a concurrent updater. Stronger levels can reject a transaction with a serialization failure, which the application must retry as a whole. Isolation is therefore a contract involving both the database and the retry behavior of its caller.