SQL transactions

A transaction groups supported writes so you can commit or cancel them together. This guide uses the HTTP SQL interface. The currently offered PostgreSQL database login is read-only; do not use these write examples as a promise of PostgreSQL write support.

Commit related SQL writes togetherKeep schema changes outside this workflow; DDL may apply immediately and cannot be undone by rollback.

Use one SQL sessionGive this logical transaction its own session ID.
Begin and run supported operationsKeep the transaction short and inspect statement errors.
Commit or roll backA successful buffered statement is not a confirmed commit.
Verify the final stateRead the record after the transaction finishes.

Before you start

Complete the quickstart, then reuse its complete OC_URL and OC_TOKEN from the same instance. The example changes the sample product's price to 1,890 cents. Use a token authorized for that table. Add database identity headers to the request headers when required.

The default SQL transaction path is read committed. Read GET /v1/capabilities on your instance for its sql_isolation_levels notes and topology restrictions. HTTP callers supply X-OC-Session-Id. PostgreSQL connections have a separate session and support contract. Do not share a session between concurrent logical transactions.

Run one transaction

import json
import os
import uuid
from urllib.request import Request, urlopen

base = os.environ["OC_URL"].rstrip("/")
session_id = str(uuid.uuid4())
headers = {
    "Authorization": f"Bearer {os.environ['OC_TOKEN']}",
    "Content-Type": "application/json",
    "X-OC-Session-Id": session_id,
}

def sql(statement, params=None):
    body = {"sql": statement}
    if params is not None:
        body["params"] = params
    request = Request(f"{base}/sql",
                      data=json.dumps(body).encode(), headers=headers)
    with urlopen(request, timeout=30) as response:
        return json.load(response)

sql("BEGIN")
try:
    sql("UPDATE quickstart.products SET price_cents = $1 WHERE id = $2",
        [1890, "product-001"])
except Exception:
    try:
        sql("ROLLBACK")
    except Exception:
        pass  # Preserve the original error; report failed cleanup separately.
    raise

# Do not put an automatic retry around this commit.
# If its response is lost, reconcile the outcome before repeating a mutation.
receipt = sql("COMMIT")
print(receipt)
print(sql("SELECT id, price_cents FROM quickstart.products WHERE id = $1",
          ["product-001"]))

After a confirmed commit, the final query should return product-001 with price_cents: 1890. Change COMMIT to ROLLBACK to discard the buffered update instead. A successful statement before COMMIT does not prove its write is durable.

Understand the boundary

Ordinary data writes are buffered until commit. On supported current deployments, reads inside the SQL transaction see its earlier buffered writes.
Read committed is not a repeatable snapshot or a general serializable guarantee. Current single-box active/passive commit paths can reject conflicting writes; the same conflict check is not promised for every replication or cross-shard path.
DDL is different. Schema changes can apply immediately. PostgreSQL can commit earlier buffered writes at that point; HTTP SQL can keep them pending while applying the schema change. ROLLBACK cannot undo the DDL and can report an applied_at_once refusal even when it discards pending data writes. Keep schema changes outside application transactions.
Cross-node behavior depends on the deployed transaction path. Check the instance's capabilities and test the exact topology before depending on a multi-table transaction across shards.

Choose an isolation level deliberately

Higher isolation remains a scoped preview. On the HTTP SQL interface, BEGIN ISOLATION LEVEL REPEATABLE READ and BEGIN ISOLATION LEVEL SERIALIZABLE use an INSERT VALUES-only bridge: in-transaction SELECT, UPDATE, DELETE, upserts and RETURNING are refused. Serializable also requires one shard; this SQL bridge refuses a RaftQuorum commit. It is unsuitable for a read-modify-write workflow. Use the transaction HTTP endpoints for their supported point-read/write workflow.

Database protocol listeners have their own support contracts. PostgreSQL is the currently offered database-login path and remains read-only; MySQL and TDS instructions are historical references. Do not infer wire behavior from the HTTP endpoint or silently fall back to a weaker level.

Handle failures by their meaning

Failure	Recovery
Invalid SQL, permissions or a constraint violation	Roll back, fix the cause, then begin a new transaction. Retrying unchanged input is not a repair.
Confirmed transaction conflict or lost transaction	Roll back as appropriate and retry the whole logical transaction with a fresh session ID, fresh reads and bounded backoff.
Network failure while COMMIT is in flight	The outcome may be unknown. Inspect application state or a unique operation record before replaying writes. A timeout does not prove rollback.
Unsupported topology or isolation	Use a supported surface or configuration. Do not retry the same unsupported request.

Keep transactions short

Open transaction state can expire or be lost during restart or failover. Do not keep a transaction open while waiting for a person, an LLM or another service. Bound its size and handle reported limits. A transaction handle is temporary; it is not an application job ID.

For PostgreSQL client behavior, savepoints and multi-statement requests, read PostgreSQL compatibility. For explicit transaction handles and optimistic validation, continue to the transaction API.

Before you startCopy Anchor Link

Run one transactionCopy Anchor Link

Understand the boundaryCopy Anchor Link

Choose an isolation level deliberatelyCopy Anchor Link

Handle failures by their meaningCopy Anchor Link