# BEGIN TRANSACTION — all-or-nothing blocks

Turn a script into one **all-or-nothing** native transaction. The editor validates every rule at the keystroke — where DynamoDB would report an opaque cancellation after the run, the studio catches violations before the wire. Write blocks use a deliberate **two-step run** (first Run previews the plan, an unchanged second Run commits) and carry an idempotency token, so a retry is a no-op rather than a double-apply.

## Syntax

```sql
BEGIN TRANSACTION;
statement;            -- writes only, or reads only — never mixed
statement;            -- each write targets ONE item by its full key
…;                    -- up to 100 statements, each item at most once
COMMIT;
```

Extra `WHERE` conditions compile to an atomic `ConditionExpression`; arithmetic `SET` compiles to a native `UpdateExpression`; anything the native form can't express identically is an **honest refusal** naming the fix, never a silent drop. Clock functions share one instant across the block. Guide: [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/).

## Use cases

### Move money with a balance guard

Debit, credit, and the audit event land together or not at all — and the `balance >= 100` predicate becomes a native condition, so an uncovered debit cancels the whole block.

```sql
BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE' AND balance >= 100;
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
INSERT INTO "stage.Outbox" VALUE {'pk': 'Outbox#transfers', 'sk': 'TX#124', 'amount': 100};
COMMIT;
```

_Executes as:_ TransactWriteItems · [ UpdateItem ×2 · PutItem ×1 ] · ClientRequestToken set

- A cancellation reports **per-statement reasons** instead of DynamoDB's opaque error; the status line reports the billed capacity (`· 3 WCU`).

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-transactions/#guarded-transfer

### Create related items together

A new order plus the outbox event that announces it — two tables that must never disagree, written as one. The transactional-outbox pattern, in two statements.

```sql
BEGIN TRANSACTION;
INSERT INTO "stage.Orders" VALUE {'pk': 'Tenant#acme', 'sk': 'Order#9007',
  'status': 'open', 'createdAt': CURRENT_TIMESTAMP};
INSERT INTO "stage.Outbox" VALUE {'pk': 'Outbox#demo', 'sk': 'EVT#0002',
  'Type': 'OrderCreated', 'Status': 'pending', 'CreatedAt': CURRENT_TIMESTAMP};
COMMIT;
```

_Executes as:_ TransactWriteItems · PutItem ×2 — both stamped with the same instant

- Clock functions render **one shared instant** across the block — the two timestamps can't drift, even across tables.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-transactions/#atomic-entity-create

### Read two accounts from one consistent snapshot

Comparing balances mid-transfer lies unless both reads see the same instant — a read transaction serves every item from a single snapshot, which no sequence of individual reads can guarantee.

```sql
BEGIN TRANSACTION;
SELECT * FROM "stage.Users" WHERE pk = 'User#9' AND sk = 'PROFILE';
SELECT * FROM "stage.Users" WHERE pk = 'User#12' AND sk = 'PROFILE';
COMMIT;
```

_Executes as:_ TransactGetItems · 2 exact-key reads · one consistent snapshot

- Reads skip the two-step ritual (nothing to double-apply) and run fine against a **read-only stage**. Each SELECT must be an exact key lookup — no extra predicates, `LIMIT`, `ORDER BY`, or aggregates.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-transactions/#snapshot-read

## Compose a transaction with reads using THEN

```sql
SELECT pk, balance
FROM "stage.Users"
WHERE pk IN ('User#9', 'User#12') AND sk = 'PROFILE';

THEN

BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE' AND balance >= 100;
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
COMMIT;

THEN

SELECT pk, balance
FROM "stage.Users"
WHERE pk IN ('User#9', 'User#12') AND sk = 'PROFILE'
ORDER BY pk;
```

A standalone `THEN` line connects `SELECT` and complete **write transaction** units into one success-gated workflow. Units run top to bottom and the first failure skips everything after it. This makes “read before, commit, verify after” explicit without pretending the three units share one transaction.

- The first `SELECT` is an independent read. If it fails, the transaction never starts.
- The `BEGIN … COMMIT` block is the only atomic unit. Once it commits, later failures cannot roll it back.
- The final `SELECT` starts only after the transaction succeeds. If it fails, the status reports **partial completion** because the transaction already committed.
- A workflow containing writes uses a two-step confirmation for the whole unchanged workflow; a read-only workflow runs immediately.

> **THEN is not GO:** `GO` is a client batch separator. DynoStudio's `THEN` is a fail-fast dependency: it means “run the next unit only if the earlier units succeeded.” Neither keyword commits a transaction — only `COMMIT` does that.

## Try it

**Try it · Transaction**

```sql
BEGIN TRANSACTION;
UPDATE "stage.Users" SET balance = balance - 100
  WHERE pk = 'User#9' AND sk = 'PROFILE' AND balance >= 100;
UPDATE "stage.Users" SET balance = balance + 100
  WHERE pk = 'User#12' AND sk = 'PROFILE';
COMMIT;
```

_Executes as:_ TransactWriteItems · 2 writes — All-or-nothing: if User#9 can't cover the debit, neither write lands. Two-step run: first Run validates and previews; an unchanged second Run commits.

_Result:_ ✓ Committed · 2 writes applied atomically · 2 WCU · idempotency token retained

_Run it live in DynoStudio:_ https://dynostudio.dev/dynostudio/

> **The block rules, up front:** Writes only or reads only — never mixed (DynamoDB's rule, caught in the editor). Each `UPDATE` / `DELETE` targets exactly **one item by its full key**, each item appears **at most once**, and the block caps at **100 statements**. `RETURNING` isn't available inside a transaction. Inside a `THEN` workflow, only complete **write** transaction blocks are allowed; a read-only transaction remains a standalone snapshot-read operation.

Related: [Writes & transactions](https://dynostudio.dev/docs/dynostudio-partiql-writes/) · [UPDATE](https://dynostudio.dev/docs/dynostudio-update/) · [INSERT & upserts](https://dynostudio.dev/docs/dynostudio-insert/) · [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/).

---
_Source: https://dynostudio.dev/docs/dynostudio-transactions/ · DynoStudio Docs_