Prisma ORM Interview Questions: Transactions, Nested Writes, and Concurrent Updates

An order endpoint creates an order and two items. That part succeeds. A later request from another browser tab changes the same order's status, and one update silently overwrites the other. Someone proposes “put it in a Prisma transaction.” That solves one problem—grouping database writes—but does not automatically solve the stale-read race. A good Prisma interview answer names both contracts separately.

This guide uses an original practice case, not a candidate report from any employer. The code examples are pinned to the Prisma Client v6 transaction API with an assumed relational schema; generated types and behavior must be checked against the actual installed Prisma version and database connector. Current Prisma documentation and releases can differ. The order schema, conflicts, and proposed policies below are scenario assumptions or engineering inferences. Start with Explain database transactions and ACID if the database boundary itself is unfamiliar.

The interview fixture: an order with a version

Assume Order has a generated id, a status, an integer version, and related OrderItem rows. Each item has sku and quantity, and the database has a unique constraint on (orderId, sku). One request creates a DRAFT order with two items. A second request submits the draft and creates an invoice. Two browser tabs may attempt that transition from the same observed version. Payment capture and email are external actions; they are not automatically part of a database transaction.

Before choosing an API, ask for the invariant: must the order and items appear together? May the invoice exist only after a successful DRAFT-to-SUBMITTED transition? Can a user safely submit twice after a timeout? Which database and Prisma Client version are installed? Does the request need a generated order ID for a second operation? The Prisma v6 transaction guide distinguishes dependent writes, independent writes, and read-modify-write workflows. Matching that distinction to the business rule is the interview's core task.

The urgent questions are: what nested writes guarantee, when $transaction([]) suffices, when an interactive callback is needed, why an atomic transaction may still allow an overwrite, how to detect a version conflict, and how to retry without duplicating side effects. The useful answer traces both rows and competing requests.

Question 1: When is a nested write the right unit?

For this v6 relational fixture, the parent order ID is generated by the database and the child items depend on it. Prisma's relation-query documentation says a relation nested write performs related mutations in one transaction: if any part fails, all its database writes roll back. The shape is one top-level create with child create operations:

// Prisma Client v6-style sketch; schema fields are fixture assumptions.
const order = await prisma.order.create({
  data: {
    status: 'DRAFT',
    version: 0,
    items: {
      create: [
        { sku: 'A', quantity: 1 },
        { sku: 'B', quantity: 2 },
      ],
    },
  },
});

The items relation and required fields must match the generated client. The important property is not a particular naming convention: either the order and both items commit, or none does. In the fixture, change the second item to a duplicate SKU under the stated unique constraint. The database rejects that child write and the order is not left behind. Test the resulting database state, not only the thrown error. This is the first article-specific exercise: draw the parent-child dependency and predict rollback after each possible failure point.

Nested write does not mean “nested transaction” in the sense of savepoints. It is a relational mutation inside one transaction. Prisma ORM v7.5's nested-transaction savepoint change concerns calling a transaction from an interactive transaction client in a newer version. Confusing that with items: { create: ... } leads to the wrong answer about scope and rollback. Pin the API and version before using either term in code review.

The v6 docs also say relation nested writes are available on top-level model create and update, while top-level bulk updateMany/deleteMany do not support the same nested-write structure. Do not promise that every bulk method can express the parent-child operation. If the records already have application-generated IDs and independent mutations, $transaction([]) may fit; the need to pass a database-generated result into a later operation changes that decision.

Question 2: How is $transaction([]) different from an interactive transaction?

The Prisma v6 guide shows $transaction([queryA, queryB]) for a sequence of Prisma Client queries in one transaction. It is useful when the operations are already known and do not depend on a result computed by a previous query in the array. For two unrelated status maintenance writes that must commit together, it can express the atomic unit cleanly. It does not give ordinary JavaScript code inside the array a way to read a generated ID from the first pending operation and construct the second based on that result.

An interactive transaction passes a callback and a transaction-scoped client, conventionally tx. Code inside can read a row, branch, and issue later queries through tx. All intended database queries must use that tx client; accidentally calling the outer prisma instance for one step can put the step outside the intended transaction. A failure thrown from the callback rolls back its database writes. The callback is an escape hatch for dependent decisions, not a reason to put an HTTP payment call inside an open database transaction.

await prisma.$transaction(async (tx) => {
  const current = await tx.order.findUnique({ where: { id: orderId } });
  if (!current) throw new Error('missing order');
  // Subsequent database decisions use tx, not the outer prisma client.
});

Keep the transaction short. Prisma's docs warn that long interactive transactions hurt performance and may deadlock. A network call to a payment provider can hang while holding database resources; it also cannot be rolled back by the database. For this exercise, decide the database transition and invoice record atomically, then coordinate payment and email with a separate durable workflow or outbox if needed. That is an architecture inference from the atomicity boundary, not a claim that Prisma ships an outbox automatically.

Question 3: Why can two successful transactions still overwrite work?

Imagine tabs A and B both read Order 42 at status=DRAFT, version=7. Each computes a change from the same old state. If the application writes the entire record by id alone, one update may replace the other's intended values, depending on the database's isolation and the statements used. Wrapping each request in its own transaction gives each an atomic commit or rollback; it does not make the two requests one serialized business decision by default. The lost-update risk is visible only when you draw the interleaving.

Time	Tab A	Tab B	State if both write by ID only
1	Reads version 7	Reads version 7	Both plan from stale shared state
2	Writes update and commits	—	A's change becomes current
3	—	Writes its old plan and commits	B can overwrite A's change

That table is a possible fixture history, not a universal statement about every isolation level and database. Database defaults vary. The Prisma v6 transaction reference describes configurable isolation when supported by the connector and discusses optimistic concurrency control for read-modify-write. The interviewer's question should be “What invariant must survive concurrency?” rather than “Which isolation level sounds strongest?”

If the mutation is simply an increment, a database-side atomic increment may avoid a read-then-write race. If the update depends on the observed status and other fields, compare-and-swap with a version token is clearer. A constraint can enforce uniqueness, but it cannot express every cross-row or application rule. Choose the smallest mechanism that guards the stated invariant and test it under simultaneous requests.

Question 4: What does a versioned conditional update prove?

For the pinned v6 API, use a conditional updateMany and inspect its count. The Prisma docs' OCC section recommends a version field to detect a row that changed since read. This is a sketch for the assumed schema:

const changed = await prisma.order.updateMany({
  where: { id: orderId, version: expectedVersion, status: 'DRAFT' },
  data: { status: 'SUBMITTED', version: { increment: 1 } },
});
if (changed.count !== 1) throw new ConflictError();

ConflictError is an application-defined error in this sketch. The result count of one means this conditional update matched and changed one row under the database's execution semantics. Zero means the row is missing, its version is stale, or its status is no longer DRAFT; a follow-up read can classify the cause for the client. Do not report zero as a successful submission. If the invoice creation must commit only with that transition, execute both through tx in one interactive transaction and throw when count is zero. The second article-specific exercise is to predict exactly one winner when both tabs submit version 7, and no orphan invoice for the loser.

An OCC conflict is not necessarily an error to retry blindly. For an editor changing a shipping address, surface “this order changed; reload and review” rather than silently replacing the user's intent. For a safe deterministic operation, a bounded retry may re-read state and recompute the decision. Either way, a version increment must happen in the same conditional write or transaction that changes the protected data. Updating the version in a separate statement reopens the race.

Question 5: What if Serializable aborts a transaction?

The v6 transaction guide documents isolationLevel where the database supports it. It says a write conflict or deadlock can surface as Prisma error P2034, and its example retries the transaction. Retry the whole read-decide-write unit, not only the final update, because an earlier read may now be stale. Bound the number of attempts, back off under contention, and expose failure when the budget is exhausted. Isolation levels and error behavior depend on the connector and version; verify them in the deployment actually used.

Do not conflate two conflicts. In the OCC example, updateMany returning count=0 is an application-detected compare-and-swap miss. P2034 is a database transaction failure such as a write conflict or deadlock in the documented v6 path. They may need different user-facing handling. A serialization retry should not contain a payment charge or email that could run multiple times when the transaction callback re-executes.

The client can also retry an HTTP request after the database committed but before the response arrived. A transaction cannot prevent a second request from creating a second order unless there is a stable idempotency key or unique business identifier and a defined replay policy. Put a unique key in the database and return the previously recorded result for a duplicate compatible request; reject a reused key with a different payload. This is an application design, not an automatic guarantee of Prisma's transaction API.

A test matrix for the order workflow

Run these tests against the pinned Prisma version and the actual database connector. A mock client can test branch logic but cannot prove isolation or constraint behavior. Use controlled concurrent transactions or barriers so both requests read the same initial version before either writes.

Scenario	Database assertion	Client or worker assertion
Parent plus two valid items	One order and exactly two linked items	Return committed order ID.
Second item violates (orderId, sku) uniqueness	No order or item from that nested write	Classify constraint error; no success response.
Two tabs submit version 7	One transition to version 8 and one conflict	Losing tab must refresh or intentionally retry.
Invoice insert fails after conditional transition in tx	Transition rolls back; no invoice	No “submitted” confirmation.
Serializable transaction returns P2034	No partial commit from aborted attempt	Retry whole transaction within a small budget.
Response lost after commit, same idempotency key sent again	One durable business operation	Replay compatible result; no duplicate external side effect.

Measure connection waits and transaction duration as well as correctness. Holding a transaction open across network calls can starve a pool even when every individual SQL statement is fast. Count OCC conflicts and serialization retries separately; a spike in one points to a different bottleneck from a spike in the other. Keep sensitive order data out of diagnostic logs while preserving request and transaction correlation.

A concise answer to give the interviewer

“For dependent order-and-item creation in this pinned Prisma Client API, I would use a relation nested write so the rows commit or roll back together. For a read-decide-write transition with an invoice, I would use a short interactive transaction through tx and a conditional version update. One winner increments the version; the loser sees zero rows changed and gets a conflict rather than silently overwriting. If a supported Serializable transaction aborts with P2034, I retry the whole unit with a bound. External payment and email require their own idempotent, durable coordination.”

That explanation connects the API to the invariant. If the interviewer changes the Prisma version or database, verify generated types, supported isolation levels, and error codes before pasting the sketch. If they ask about “nested transactions,” clarify whether they mean relation nested writes or actual nested transaction/savepoint behavior.

Sources and further reading

• Prisma Client v6: Transactions and batch queries — nested writes, $transaction, OCC, isolation, and P2034 handling.
• Prisma Client v6: Relation queries — relational nested writes and rollback guarantee.
• Prisma Client v6 API reference — generated update filters and transaction options.
• Prisma ORM v7.5 changelog — newer nested-transaction savepoint behavior, distinct from relation nested writes.