Transactions, Normalization, and Performance

Project Checkpoint: Moving Cards Between Decks

---

The previous chapter built study mode — a card-at-a-time review flow that exercises transactional atomicity. The user submits a rating, the route records it, and the redirect picks the next card from the current state of the database. The transaction in that route makes sure that if anything fails between the lookup and the insert, neither happens.

This checkpoint exercises a different concern: concurrent access. The feature is moving a card from one deck to another. The route does a multi-step read-then-write: read the target deck to validate it exists, read the source card to confirm it exists, then update the card’s deck_id. The hazard here isn’t internal failure — it’s the possibility that another transaction could interfere between the reads and the write.

This is the second of two project checkpoints in this part. The previous checkpoint focused on transactional atomicity — making the write either fully happen or fully not. This checkpoint focuses on isolation under concurrent access — making sure two users acting at the same time cannot corrupt each other’s work, even when each one’s individual route is correct.

The chapter is a worked example, like the previous one. By the end, your project should let users move cards between decks, with the move running safely under explicit row locking and a fast post-move deck-detail page.

What the Feature Will Look Like

Before any code, it helps to picture what the feature does from the user’s perspective.

The project’s existing deck detail page lists the cards in a deck. After this feature is in place, each card row gets a small “Move to…” dropdown listing the other decks, with a “Move” button next to it. Picking a target deck and clicking the button moves the card; the page reloads showing the target deck, and the card no longer appears in the source deck (it now lives in the target deck).

That is the whole feature. There is no batch-move, no drag-and-drop, no undo button. The user sees one card, picks one target deck, clicks one button.

Concretely, the feature has four pieces:

1. a POST route that performs the move, locking both the target deck (so no other transaction can delete it while we’re moving cards into it) and the source card (so no other transaction can move the same card at the same time),
2. a small form on each card row in the deck detail template, with a dropdown of the other decks,
3. a small change to the deck detail route to load the list of other decks for the dropdown,
4. an end-to-end check that confirms the move works, the locks block as expected, and the post-move deck detail page loads fast.

No new migration is needed. The previous checkpoint’s 008_indexes.sql already added the index on cards(deck_id) that the post-move deck-detail page depends on.

Each piece is small. The chapter walks through them one at a time.

Four Concerns in This Feature

Each part-level idea shows up somewhere in this feature, and naming where it shows up before writing the code makes the implementation choices clearer.

Concurrency (focus of this checkpoint). The move route does three things in sequence: it reads the target deck to validate it, it reads the source card to confirm it exists, and it updates the card’s deck_id. Between any two of these steps, another transaction could in principle modify the data the route is acting on. The two scenarios worth defending against:

• The target deck disappears between validation and write. Another transaction deletes the target deck after we read it but before we update the card. Without protection, the update either fails at commit (foreign-key violation) or, depending on timing, succeeds against a deck that no longer exists — both bad outcomes.

• The card is moved concurrently by another request. Two users (or two browser tabs of the same user) submit move requests for the same card at roughly the same time. Without protection, both requests succeed, the last write wins, and the first move is silently overwritten.

SELECT ... FOR UPDATE on each of the two relevant rows — the target deck row and the source card row — prevents both scenarios. The locks make the route’s read-then-write pattern safe even under concurrent access.

Transactions. The validation reads and the write are all inside a single with conn.transaction(): block, just like in the previous checkpoint. The transaction is what makes the locks meaningful: a FOR UPDATE lock is held until the transaction commits or rolls back. Without a transaction wrapping the locks, they would be released immediately after each SELECT and provide no protection.

Schema quality. The move changes one column on one row. There is no denormalized card_count to update on either deck; if such a count were needed, it would be derived from COUNT(*) FROM cards WHERE deck_id = ?. The schema’s lack of denormalized counts is what makes the move so simple: one write, no maintenance.

Performance. After the move, the deck detail page reloads. That page runs SELECT ... FROM cards WHERE deck_id = ?, which benefits from the index on cards(deck_id) added in 008_indexes.sql during the previous checkpoint. No new index is needed for this checkpoint.

Concurrency is the concern this checkpoint actively verifies; the other three are present but not the focus.

Step 1: The Move Route

The first piece is the POST route that performs the move:

@app.post("/cards/{card_id}/move")
def move_card(card_id: int, target_deck_id: int = Form(...)):
    with get_connection() as conn:
        with conn.transaction():
            # Lock the target deck while we validate it exists.
            target_deck = conn.execute(t"""
                SELECT id FROM decks WHERE id = {target_deck_id} FOR UPDATE
            """).fetchone()

            if target_deck is None:
                return RedirectResponse(
                    url=f"/cards/{card_id}", status_code=303
                )

            # Lock the source card so no concurrent move can change it.
            card = conn.execute(t"""
                SELECT id, deck_id FROM cards WHERE id = {card_id} FOR UPDATE
            """).fetchone()

            if card is None:
                return RedirectResponse(url="/decks", status_code=303)

            if card.deck_id == target_deck.id:
                # No-op move; redirect back without writing.
                return RedirectResponse(
                    url=f"/decks/{card.deck_id}", status_code=303
                )

            conn.execute(t"""
                UPDATE cards SET deck_id = {target_deck_id} WHERE id = {card_id}
            """)

    return RedirectResponse(url=f"/decks/{target_deck_id}", status_code=303)

The route is short, but each piece has a reason.

The decorator is @app.post, because moving a card is a write. Match the conventions from previous chapters.

The with conn.transaction(): block wraps the whole sequence: the two locking reads, the no-op check, and the update. The transaction is what makes the locks meaningful — they are held only as long as the transaction is open.

Two Locks

The two SELECT ... FOR UPDATE statements are the heart of this route. Each one locks one row for the duration of the transaction. While the lock is held, no other transaction can UPDATE or DELETE the locked row, and no other transaction can take its own FOR UPDATE lock on that row — it has to wait.

The order of the locks matters.

The target deck is locked first. This prevents another transaction from deleting the target deck between our validation read and the eventual UPDATE. If we waited until after the read of the card to lock the deck, there would be a window between the deck validation and the deck lock where the deck could be deleted by another transaction; the lock would then succeed against nothing or fail entirely.

The card is locked second. This prevents another transaction from concurrently moving the same card. Without this lock, two simultaneous move requests for the same card would each read the card, each compute “the card is currently in deck X, I want to move it to deck Y,” and each issue an UPDATE. The two updates would serialize at the database, but the first move would be silently overwritten by the second. With the lock, the second request waits until the first commits, then sees the card already in deck Y (or wherever the first request moved it).

Locking in a consistent order — target deck first, then card — matters for a reason that comes up in the section on deadlocks below.

Three Guard Clauses

Three guard clauses precede the actual update: target deck does not exist, card does not exist, and the move is a no-op (target equals current deck). Each one redirects without writing.

The target-deck guard handles malformed input (a stale URL with a deleted deck id) or a deck that was deleted between when the form was rendered and when the form was submitted. The redirect goes back to the card’s URL so the user can pick a different target.

The card guard handles input where the card id doesn’t exist. The redirect goes to /decks because we don’t have a sensible card-specific destination.

The no-op guard avoids issuing an UPDATE when the user picks the deck the card is already in. The redirect goes to the current deck, which is also the “target” (they’re the same).

All three guards exit the with conn.transaction(): block via return. The transaction commits as it exits — but in each guard case, no writes have happened, so the commit is a no-op. The locks held by the FOR UPDATE reads are released at commit, freeing them for other transactions.

The Update and the Redirect

If all three guards pass, the route issues the UPDATE and the transaction commits when the with block exits. The redirect goes to the new deck’s detail page, so the user lands on the deck the card just moved into.

Both SQL statements use template-string parameterization. Neither value is concatenated into the SQL string directly, which prevents SQL injection. This matches the conventions used throughout the project.

The redirect uses status code 303 (“See Other”), the standard pattern for “after a successful POST, redirect to a GET” — it prevents the browser from re-submitting the form if the user reloads the resulting page.

Step 2: The Form on the Deck Detail Template

The form lives in the existing deck detail template, inside the loop that renders each card:

<h1>{{ deck.name }}</h1>

<ul class="cards">
{% for card in cards %}
  <li>
    <strong>Q:</strong> {{ card.question }}<br/>
    <strong>A:</strong> {{ card.answer }}<br/>

    <form method="post" action="/cards/{{ card.id }}/move">
      <label>
        Move to:
        <select name="target_deck_id" required>
          <option value="">--</option>
          {% for other_deck in other_decks %}
            <option value="{{ other_deck.id }}">{{ other_deck.name }}</option>
          {% endfor %}
        </select>
      </label>
      <button type="submit">Move</button>
    </form>
  </li>
{% endfor %}
</ul>

The form submits to /cards/{{ card.id }}/move, which matches the POST route. The name="target_deck_id" matches the route’s target_deck_id: int = Form(...) parameter.

The dropdown lists other_decks — every deck except the one currently being viewed. The route already handles the no-op case (target equals current deck) gracefully, so a slightly out-of-date other_decks list would not corrupt anything; it would just produce a redirect without a write.

For this template to work, the deck detail route needs to load other_decks alongside the existing deck and cards:

@app.get("/decks/{deck_id}")
def deck_detail(request: Request, deck_id: int):
    with get_connection() as conn:
        deck = conn.execute(t"""
            SELECT id, name FROM decks WHERE id = {deck_id}
        """).fetchone()

        cards = conn.execute(t"""
            SELECT id, question, answer
            FROM cards
            WHERE deck_id = {deck_id}
            ORDER BY id
        """).fetchall()

        other_decks = conn.execute(t"""
            SELECT id, name FROM decks WHERE id <> {deck_id} ORDER BY name
        """).fetchall()

    return render(
        request, "deck_detail.html",
        deck=deck, cards=cards, other_decks=other_decks,
    )

Three queries, each with one purpose. The route reads the deck, the cards, and the list of other decks; the template renders them.

Step 3: Confirming the Supporting Index

The post-move deck detail page reloads with SELECT ... FROM cards WHERE deck_id = ?. This benefits from the index on cards(deck_id) that was added in 008_indexes.sql during the previous checkpoint. No new migration is needed.

Confirm the index exists with \di in psql:

=> \di cards_deck_id_idx
                                  List of relations
 Schema |        Name        | Type  | Owner | Persistence | Access method | Table
--------+--------------------+-------+-------+-------------+---------------+-------
 public | cards_deck_id_idx  | index | ...   | permanent   | btree         | cards

If the index is missing, the migration chain has not been fully applied and the reproducibility check below will surface that.

Deadlocks and Consistent Lock Order

A note worth pausing on before the verification.

Locking two rows in two different tables creates the potential for deadlock: if one transaction locks deck X then tries to lock card C, while a second transaction has locked card C then tries to lock deck X, both transactions wait forever. PostgreSQL detects this and rolls one of them back with a deadlock error.

The route above locks in a consistent order: target deck first, then source card. As long as every route that locks both a deck row and a card row uses the same order, deadlock is impossible: two transactions trying to acquire the same two locks will queue, not deadlock.

For this project, the move route is the only route that takes both kinds of lock, so deadlock is not an active concern. But the discipline — establish and document a lock order, then follow it everywhere — is what keeps deadlock impossible as the project grows. The Concurrency and Isolation chapter that discussed deadlocks introduced this principle in the abstract; here it applies concretely to the project.

End-to-End Verification

The feature is now in place. Before submitting, walk through the full path once with a fresh database to confirm everything works.

docker compose down -v
docker compose up --build

The down -v removes the database volume, so the next up starts from an empty PostgreSQL instance. The migrations run from 001_schema.sql through to 008_indexes.sql, and the application starts. If anything in the migration chain or the seed data is broken, this is where it surfaces.

Once the application is up, walk through the feature:

1. Open http://localhost:8000/decks and pick a deck.
2. The deck detail page should show its cards, each with a “Move to…” dropdown. The dropdown should list every other deck.
3. Pick a card, choose a target deck, and click Move. The page should reload showing the target deck, and the card should appear there. The source deck should no longer contain the card.
4. Pick the same card on the target deck and move it back. The card should land in the original deck again.

Then verify the concurrency concern explicitly.

Verification A: the target-deck lock blocks concurrent access. This verification needs two terminals. In the first one, open psql and hold a lock on a target deck:

=> BEGIN;
=> SELECT id FROM decks WHERE id = 1 FOR UPDATE;
 id
----
  1
(1 row)

The transaction is now holding a lock on deck 1. In a browser (or a second terminal), try to move a card to deck 1. The request should hang — the route is blocking on its own FOR UPDATE of deck 1, waiting for the first session’s lock to release. The request will not time out on its own; it will wait as long as the lock is held.

Back in the first psql session, release the lock:

=> ROLLBACK;

The browser request should immediately complete and redirect to deck 1. The move went through as soon as the lock was free.

This verification proves the target-deck lock does real work. Without FOR UPDATE on the target-deck read, the second request would not have waited, and the deck could in principle have been deleted while the move was in progress.

Verification B: the card lock blocks concurrent moves of the same card. Same shape, different target. In one psql session:

=> BEGIN;
=> SELECT id, deck_id FROM cards WHERE id = 1 FOR UPDATE;
 id | deck_id
----+---------
  1 |       2
(1 row)

In the browser, try to move card 1 to a different deck. The request should hang. ROLLBACK in psql; the browser request should complete.

This verification proves the card lock does real work. Without it, two concurrent move requests for the same card could both succeed, and the first move would be silently overwritten by the second.

Verification C: the post-move page is fast. Run EXPLAIN ANALYZE on the deck detail’s card lookup:

EXPLAIN ANALYZE SELECT id, question, answer FROM cards WHERE deck_id = 1 ORDER BY id;

The output should show Index Scan using cards_deck_id_idx, not Seq Scan. If it shows Seq Scan, either the index is missing (run \di cards_* to check) or the table is too small for the optimizer to bother (in which case adding a few hundred more cards in seed data and running again should switch the plan to an index scan).

If any of these verifications does not behave as expected, the issue is somewhere in the chain you just built. Each piece has one job; whichever output is wrong points at which piece to look at.

What Got Built, Concretely

This chapter took the toolkit from the rest of the part and surfaced it in one defensive feature:

• one new route (the POST that moves a card, with two FOR UPDATE locks and validation),
• one modified route (the GET that now loads other_decks for the dropdown),
• one modified template (the deck detail page, with the move form per card),
• no new migrations (the index on cards(deck_id) was added in the previous checkpoint).

The schema did not gain a single column. Every effect of the move is visible through the existing schema; nothing was denormalized to support the page.

The project now lets users reorganize their cards across decks, with the operation running under transactional discipline (no half-finished moves), without introducing schema drift (no denormalized counts to maintain), with the post-move display supported by an index (so the deck detail page stays fast as the data grows), and — the distinctive contribution of this checkpoint — with explicit row locking that prevents concurrent moves and concurrent deck-deletions from corrupting each other’s work.

Together with the study mode from the previous checkpoint, the project now has two write flows that exercise different transaction concerns: the study-mode review records a single rating under transactional atomicity (the previous checkpoint), and the move-card operation guards against concurrent collisions through FOR UPDATE locks (this checkpoint). The student tracker has gone from a static reference of decks and cards to a working application that handles writes safely.

Common Pitfalls

A few places where this kind of feature commonly goes wrong:

• locking the target deck after the validation read instead of as part of it — a window opens between the read and the lock during which the deck could be deleted by another transaction,
• forgetting to lock the source card — two concurrent move requests for the same card could both succeed, and the first move would be silently lost,
• locking the rows in an inconsistent order across different routes — creates the possibility of deadlock (transaction A locks deck X then waits for card C; transaction B locks card C then waits for deck X),
• forgetting that the lock is held only inside the transaction — a SELECT ... FOR UPDATE outside with conn.transaction(): would release the lock immediately and provide no protection,
• updating a denormalized card_count on either deck without consistency machinery (the project does not have such a count, so this does not apply here, but the pattern would appear in any feature that introduced one),
• skipping the reproducibility check (docker compose down -v followed by docker compose up --build) and so missing a broken migration chain or seed,
• skipping Verification A and B — they are the only verifications that surface lock-related bugs, and bugs of that kind are otherwise invisible until production.

Each of these is a small habit. Together they are the difference between a feature that works in single-user testing and a feature that handles real concurrent access.

Submission

The deliverable for this checkpoint is the project zip, including the new move route, the modified deck-detail route and template, and the study mode from the previous checkpoint. Before submitting, confirm the reproducibility check above passes from a clean state.

Loading Exercise...

Check Your Understanding

1. Why does the move route lock the target deck with SELECT ... FOR UPDATE rather than just reading it? What concurrent failure does this lock prevent that a plain SELECT would not?
2. Why is the source card also locked with FOR UPDATE? What concurrent failure does this second lock prevent that locking only the target deck would not?
3. Why does this checkpoint use FOR UPDATE locks, while the previous checkpoint (study mode) did not need them, even though both routes wrap multi-statement work in transactions?