reference · transactions

Transactions

A transaction groups multiple SQL statements into one atomic unit: either every write lands, or none do. Transactions run over the same POST /v1/tenants/:t/sql endpoint you already use - and they may span tables anywhere in your configuration, including tables that live on different nodes. Commit is atomic either way.

The lifecycle.

Send BEGIN, then your statements, then COMMIT (or ROLLBACK) - each as its own POST /sql call. What ties them together is the X-OC-Session-Id header: every statement carrying the same session id belongs to the same transaction. Any unique string works; generating a UUID per transaction is the simple, collision-free choice.

POST /v1/tenants/:t/sql - a full transaction

# Every statement goes to the same endpoint. A transaction is a
# sequence of statements that share one X-OC-Session-Id header.
SID=$(uuidgen)   # any unique string; a UUID per transaction is the easy choice

# 1. Open the transaction
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "BEGIN" }'

# 2. Run statements - they are part of the transaction because they
#    carry the same session id. Tables may live on different nodes.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO shop.orders (id, customer, amount_cents) VALUES ('\''o-1'\'', '\''c-9'\'', 4200)"
  }'

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "UPDATE shop.inventory SET reserved = 1 WHERE id = '\''sku-7'\''"
  }'

# 3. Commit - everything above becomes visible at once, or nothing does
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "COMMIT" }'

Nothing written inside the transaction is visible to other readers until COMMIT returns 200. If COMMIT succeeds, every statement's writes are durable and visible together.

What's guaranteed.

Guarantee	What it means for you
atomic commit	All writes in the transaction become visible together, or none do. There is no state in which a reader sees half of them.
spans nodes	On a multi-node configuration, a transaction can touch tables that live on different nodes. The atomicity guarantee is identical - your code doesn't change.
no partial writes	If anything goes wrong - a statement error, a lost transaction, a failed commit - zero writes from the transaction are applied.
safe rollback	`ROLLBACK` is idempotent and always returns 200 - even if the transaction no longer exists. It is always safe to call in a cleanup path.

Errors inside a transaction.

If a statement inside a transaction fails - a constraint violation, an unknown column, a type mismatch - the transaction is poisoned. You can't paper over the error and keep going: a later COMMIT fails cleanly, and no writes from the transaction are applied. The correct response is to ROLLBACK, fix the statement, and run the whole transaction again.

Separately, a transaction can be lost - for example when the service restarts while your transaction is open. In that case the next statement you send returns 409 with "transaction not found". Nothing was committed. Treat 409 as a signal to ROLLBACK (safe, idempotent) and retry the entire transaction from BEGIN with a fresh session id.

You see	What happened	Do this
4xx on a statement	The statement failed; the transaction is poisoned. COMMIT will now fail cleanly.	ROLLBACK, fix the statement, retry the whole transaction.
409 "transaction not found"	The open transaction was lost (e.g. a service restart, or the idle timeout below). Nothing was committed.	ROLLBACK, then retry the whole transaction with a fresh session id.
COMMIT fails	The transaction could not be applied. No partial writes exist.	ROLLBACK, retry the whole transaction.

Idle timeout.

An open transaction that sits idle for 5 minutes is discarded. The next statement on that session id returns 409 "transaction not found", and nothing was committed. Transactions are for grouping writes, not for holding application state - keep them short, and don't hold one open across user think-time.

The retry pattern.

Because a lost or poisoned transaction never leaves partial writes behind, the safe recovery is always the same: ROLLBACK, then re-run the whole transaction. Wrap it once and reuse it:

run a transaction with automatic retry

import uuid, requests

def run_transaction(base, tenant, token, statements, max_retries=3):
    """Run a list of SQL statements as one atomic transaction.
    Retries the WHOLE transaction from BEGIN if it is lost mid-flight."""
    for attempt in range(max_retries + 1):
        sid = str(uuid.uuid4())          # fresh session id per attempt
        headers = {
            "Authorization": f"Bearer {token}",
            "X-OC-Session-Id": sid,
        }
        url = f"{base}/v1/tenants/{tenant}/sql"

        def sql(stmt):
            return requests.post(url, headers=headers, json={"sql": stmt})

        sql("BEGIN")
        ok = True
        for stmt in statements:
            r = sql(stmt)
            if r.status_code == 409:     # transaction not found - it was lost
                ok = False
                break
            r.raise_for_status()         # statement errors poison the tx
        if ok:
            commit = sql("COMMIT")
            if commit.status_code == 200:
                return commit.json()

        sql("ROLLBACK")                  # always safe - idempotent, returns 200
        if attempt == max_retries:
            raise RuntimeError("transaction failed after retries")

async function runTransaction(
  base: string, tenant: string, token: string,
  statements: string[], maxRetries = 3,
) {
  for (let attempt = 0; attempt <= maxRetries; attempt++) {
    const sid = crypto.randomUUID();          // fresh session id per attempt
    const sql = (stmt: string) =>
      fetch(`${base}/v1/tenants/${tenant}/sql`, {
        method: "POST",
        headers: {
          "Authorization": `Bearer ${token}`,
          "X-OC-Session-Id": sid,
          "Content-Type": "application/json",
        },
        body: JSON.stringify({ sql: stmt }),
      });

    await sql("BEGIN");
    let ok = true;
    for (const stmt of statements) {
      const res = await sql(stmt);
      if (!res.ok) { ok = false; break; }     // 409 or a statement error
    }
    if (ok) {
      const commit = await sql("COMMIT");
      if (commit.ok) return commit.json();
    }

    await sql("ROLLBACK");                    // always safe - idempotent
  }
  throw new Error("transaction failed after retries");
}

common mistakes

Retrying only the failed statement. After a 409 the transaction is gone - the earlier statements in it were never committed. Retry from BEGIN, not from where you left off.
Reusing a session id across transactions. Generate a fresh id per transaction (and per retry attempt). Reuse invites cross-talk between logically separate transactions.
Holding a transaction open while waiting on something else. An LLM call, a user prompt, a slow upstream API - all of these can outlive the 5-minute idle timeout. Gather your data first, then run the transaction.
Skipping ROLLBACK because "it probably doesn't exist". ROLLBACK is idempotent and returns 200 either way. Always call it in your error path - it costs nothing and never hurts.