FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Transactions, Indexes and Concurrency
Transactions, Indexes and Concurrency Atomic workflows, query access paths and multi-user thinking
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Transactions, Indexes and Concurrency
Transactions protect multi-step invariants Many application workflows require several database changes to succeed together. If one succeeds and another fails, the database can end up representing a business state that should never exist. A transaction groups the changes into one unit of work.
A transaction groups related changes into one unit of work. BEGIN
start transaction
mark complete record event
all changes become durable discard transaction changes
Figure - COMMIT makes the unit succeed; ROLLBACK abandons it when the workflow cannot be completed safely.
BEGIN;
UPDATE tasks SET completed = true WHERE id = 42;
INSERT INTO activity_log (task_id, action) VALUES (42, 'completed');
COMMIT;
BEGIN; -- perform several related changes -- something fails or business rule is not satisfied ROLLBACK;
ACID as an application developer Property Practical meaning
Atomicity The transaction commits as one unit or rolls back as one unit.
Consistency Constraints and application rules move the database between valid states.
Isolation Concurrent transactions are controlled so their interaction has defined behavior.
Durability Committed changes survive normal process/server failures according to PostgreSQL durability mechanisms.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Transactions, Indexes and Concurrency
Concurrency means other users exist A single-user tutorial can pretend no one else changes data between your read and write. Production systems cannot. PostgreSQL uses MVCC and transaction isolation to coordinate concurrent access. You do not need deep database-internals knowledge yet, but you must stop assuming that a value you read will remain unchanged until your later UPDATE.
Optimistic concurrency checks, row locks and carefully scoped transactions exist because multiple requests can race. We introduce advanced patterns only when TaskFlow has workflows that actually need them.
Indexes are access paths Indexes are access paths - not free speed buttons.
scan many table rows navigate matching key range
faster reads can mean more storage and write work
Figure - An index can reduce lookup work when it matches the query pattern, but it has storage and maintenance cost.
By default, CREATE INDEX creates a B-tree index, which is appropriate for many equality and range searches. An index helps only when it matches how the application searches or orders data. It also consumes storage and must be maintained when rows are inserted, updated or deleted. Example indexes
CREATE INDEX tasks_project_id_idx ON tasks (project_id);
CREATE INDEX tasks_project_open_idx ON tasks (project_id, completed);
Why foreign-key columns often need deliberate indexes A foreign-key declaration does not automatically index the referencing column in PostgreSQL. If TaskFlow frequently finds tasks by project_id or PostgreSQL must locate child rows while checking a parent change/delete, an index can reduce the work. The correct index still depends on the actual workload.
Composite index order matters An index on (project_id, completed) is not equivalent to one on (completed, project_id). Index column order should reflect the query patterns, selectivity and ordering requirements. Do not collect indexes as decorations.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “Transactions Indexes and Concurrency” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “Transactions Indexes and Concurrency” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Wrap a multi-step change in a transaction and prove rollback preserves consistency.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Treating IDs as the only integrity mechanism
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.