Controlling PostgreSQL Transaction Isolation Levels with Bun.sql — Distinguishing the Role of the isolation Option and Savepoints

Two users check out the last item in stock at nearly the same moment. Both requests receive 200 OK, and inventory.qty in the DB becomes -1. The timeline below shows what the problem actually looks like.

The key point is that the default isolation level READ COMMITTED cannot prevent this scenario. Bun.sql, the Zig-based zero-dependency PostgreSQL driver introduced in Bun 1.2, lets you control the isolation level with a single line: sql.begin("isolation level serializable", ...). Yet a common misconception is that tx.savepoint(), which frequently appears in the same documentation, solves a similar problem. Isolation levels and savepoints are orthogonal concepts. One prevents concurrency anomalies; the other enables partial rollbacks within a transaction. This article distinguishes the role of each tool through code scenarios, then covers combination patterns and common mistakes.

How PostgreSQL Isolation Levels Actually Work

The SQL standard defines 4 isolation levels, but PostgreSQL effectively has only 3 distinct behaviors. READ UNCOMMITTED behaves identically to READ COMMITTED internally, because the MVCC architecture never permits Dirty Reads in the first place. For the detailed MVCC snapshot behavior, refer to Chapter 13 of the PostgreSQL official documentation; the table below is sufficient for a practical summary.

Two points deserve attention. First, PostgreSQL blocks Phantom Reads even at REPEATABLE READ (stricter than the standard). So the practical difference between REPEATABLE READ and SERIALIZABLE narrows down to a single Serialization Anomaly — write skew. Second, SERIALIZABLE layers SSI (Serializable Snapshot Isolation) on top of snapshot isolation, failing one side with SQLSTATE 40001 only when an actual conflict is detected.

Specifying Isolation Levels in Bun.sql

The first argument to sql.begin() is the option string appended after BEGIN, passed as-is. Bun handles the flow: BEGIN [options] → callback execution → COMMIT on success / ROLLBACK on exception.

// READ COMMITTED (default, same even without explicit specification)
await sql.begin(async tx => { /* ... */ });
 
// REPEATABLE READ
await sql.begin("isolation level repeatable read", async tx => { /* ... */ });
 
// SERIALIZABLE
await sql.begin("isolation level serializable", async tx => { /* ... */ });
 
// Combination: read-only + REPEATABLE READ
await sql.begin("isolation level repeatable read read only", async tx => { /* ... */ });

The string approach may feel raw at first, but it has the advantage of mapping 1:1 with PostgreSQL BEGIN syntax. Additional options like DEFERRABLE can be appended exactly as shown in the documentation. Note that postgres.js uses the same string signature — the two libraries handle isolation level specification identically.

Scenario 1 — Inventory Deduction + Order Creation (SERIALIZABLE + Retry)

The oversell that occurs under READ COMMITTED was already demonstrated in the sequence diagram at the beginning. Switching to SERIALIZABLE causes SSI to detect this pattern and fail the late-committing side with SQLSTATE 40001. The application catches this code and retries.

async function placeOrder(userId: number, productId: number) {
  for (let attempt = 0; attempt < 3; attempt++) {
    try {
      return await sql.begin("isolation level serializable", async tx => {
        const [item] = await tx`
          SELECT qty FROM inventory
          WHERE product_id = ${productId}
        `;
 
        if (!item) throw new Error("product_not_found");
        if (item.qty < 1) throw new Error("out_of_stock");
 
        await tx`
          UPDATE inventory SET qty = qty - 1
          WHERE product_id = ${productId}
        `;
 
        const [order] = await tx`
          INSERT INTO orders (user_id, product_id)
          VALUES (${userId}, ${productId})
          RETURNING *
        `;
 
        return order;
      });
    } catch (e: any) {
      if (e.code === "40001" && attempt < 2) {
        await new Promise(r => setTimeout(r, 50 * (attempt + 1)));
        continue;
      }
      throw e;
    }
  }
}

An earlier draft also used FOR UPDATE, but it was removed here. Mixing FOR UPDATE inside a SERIALIZABLE transaction sacrifices SSI's optimistic concurrency advantage (reads don't block each other) and increases deadlock risk depending on lock ordering. Use it selectively only for hot rows where contention is clearly high and early failure is preferable; otherwise, leave it entirely to SSI. Adjust the retry count and backoff to match your traffic characteristics.

Scenario 2 — Month-End Summary Report (REPEATABLE READ + READ ONLY)

If another transaction commits between multi-step aggregations, the total and per-category sums can diverge. REPEATABLE READ fixes the snapshot at transaction start, so multiple queries spanning several seconds all see the same view.

async function generateMonthlyReport(month: string) {
  return await sql.begin("isolation level repeatable read read only", async tx => {
    const [totals] = await tx`
      SELECT SUM(amount) AS total, COUNT(*) AS count
      FROM transactions
      WHERE date_trunc('month', created_at) = ${month}::date
    `;
 
    const breakdown = await tx`
      SELECT category, SUM(amount) AS subtotal
      FROM transactions
      WHERE date_trunc('month', created_at) = ${month}::date
      GROUP BY category
      ORDER BY subtotal DESC
    `;
 
    return { totals, breakdown };
  });
}

Adding read only lets PostgreSQL know the transaction will perform no writes, allowing it to skip some bookkeeping overhead. For report and dashboard query transactions, it's good practice to include it habitually.

Scenario 3 — Allowing Partial Failures in Batch Inserts (Savepoint)

When inserting thousands of events in a single transaction, you may not want to roll back the entire batch due to a few duplicate records. This is where savepoints come in.

await sql.begin(async tx => {
  let inserted = 0;
  let skipped = 0;
 
  for (const record of records) {
    await tx.savepoint(async sp => {
      await sp`
        INSERT INTO events (id, data, created_at)
        VALUES (${record.id}, ${record.data}, ${record.createdAt})
      `;
      inserted++;
    }).catch(e => {
      if (e.code === "23505") {
        // UniqueViolation — rolls back only the savepoint, outer transaction preserved
        skipped++;
        return;
      }
      throw e;
    });
  }
 
  console.log(`Inserted: ${inserted}, duplicates skipped: ${skipped}`);
});

The critical part is re-throwing non-23505 errors with throw e in .catch(). Unexpected errors must not be silently swallowed.

Scenario 4 — Combining Isolation Level + Savepoint

The two tools operate at different layers, so they can be used together. Open with SERIALIZABLE, then wrap an audit log — which is allowed to fail — in a savepoint.

async function createUser(email: string, ip: string, userAgent: string) {
  await sql.begin("isolation level serializable", async tx => {
    const [user] = await tx`
      INSERT INTO users (email) VALUES (${email}) RETURNING *
    `;
 
    // User creation is preserved even if the audit log fails
    await tx.savepoint(async sp => {
      await sp`
        INSERT INTO audit_log (user_id, action, metadata)
        VALUES (${user.id}, 'user_created', ${{ ip, userAgent }})
      `;
    }).catch(e => {
      console.error("Audit log failed, continuing:", e.message);
    });
 
    await tx`
      INSERT INTO welcome_emails (user_id, scheduled_at)
      VALUES (${user.id}, NOW() + INTERVAL '5 minutes')
    `;
  });
}

If the metadata column is jsonb, pass the object directly into the template literal. Pre-stringifying with JSON.stringify() stores it as text, which breaks JSON operators like ->> and @>. The isolation level handles concurrency anomalies and the savepoint handles partial failures within the transaction, so there is no conflict in combining them.

When to Choose What

Five Common Mistakes

1. Expecting performance gains from READ UNCOMMITTED In PostgreSQL, READ UNCOMMITTED behaves identically to READ COMMITTED. There is no performance difference.

2. Assuming REPEATABLE READ eliminates all write conflicts REPEATABLE READ fixes the read snapshot, and concurrent UPDATEs on the same row are handled with lock waits or 40001 (when the first reader later sees a different committed version). However, write skew — a Serialization Anomaly where two transactions each update the row the other read — is not prevented by REPEATABLE READ. SERIALIZABLE is required to block this.

3. Using SERIALIZABLE without implementing retry logic If you use SERIALIZABLE, retrying on SQLSTATE 40001 is mandatory, not optional. Deploying without retry logic means 5xx errors go straight to clients when traffic spikes.

4. Trying to substitute savepoints for isolation levels Savepoints are unrelated to isolation levels. No matter how finely you partition savepoints, they cannot prevent Phantom Reads or write skew.

5. Typos in the isolation level string Bun.sql passes this string directly to the server, so a typo causes a PostgreSQL syntax error. Since there is no type safety, managing these as constants is advisable.

export const ISOLATION = {
  READ_COMMITTED: "isolation level read committed",
  REPEATABLE_READ: "isolation level repeatable read",
  SERIALIZABLE: "isolation level serializable",
  REPEATABLE_READ_READ_ONLY: "isolation level repeatable read read only",
} as const;
 
await sql.begin(ISOLATION.SERIALIZABLE, async tx => { /* ... */ });

Summary

It is easy to conflate isolation levels and savepoints because they appear within the same transaction API, but the decision criterion is simple. To prevent anomalies between multiple transactions, raise the isolation option; to selectively roll back a specific block within a single transaction, use a savepoint. Both problems can appear in the same request, in which case simply use both together.

Because Bun.sql's string option approach maps directly to PostgreSQL BEGIN syntax, when in doubt it is faster to open the PostgreSQL transaction documentation than the library documentation. As of August 2026, Bun.sql has been expanded into a unified Bun.SQL API covering MySQL/MariaDB and SQLite as well, so if you're working in a multi-DB environment, check the latest support matrix in the Bun runtime SQL documentation.

References

• Bun SQL official documentation
• Bun SQL.begin method API reference
• Bun TransactionSQL TypeScript interface
• Bun SavepointSQL TypeScript interface
• Bun 1.2 release blog post
• PostgreSQL official documentation — 13.2 Transaction Isolation
• PostgreSQL official documentation — SET TRANSACTION
• PostgreSQL official documentation — SAVEPOINT
• PostgreSQL official documentation — 13.5 Serialization Failure Handling
• Bun.sql tracking issue (Postgres client) — GitHub