C3 AI Documentation Home

Use Db.Transaction to Group Writes in a Single SQL Transaction

By default, each C3 write operation (create, update, upsert, merge, remove) executes in its own SQL transaction and commits immediately. Db.Transaction lets you wrap multiple writes in a single SQL transaction so they commit or roll back as a unit.

Use this when a sequence of writes must be atomic — for example a balance transfer where the debit and credit must both succeed or both be undone.

See also Entity Write Transaction Model for the broader write pipeline.

Available in platform release 8.9.0 and later (PLAT-121642).

Basic usage

Call Db.Transaction#execute with a lambda. All entity writes performed inside the lambda share a single SQL transaction. If the lambda returns normally, the transaction commits; if it throws, the transaction rolls back.

JavaScript
Db.Transaction.execute(function() {
  var from = Account.forName('Alice');
  var to   = Account.forName('Bob');

  from.withField('balance', from.balance - 100).update();
  to.withField('balance', to.balance + 100).update();
});

If the second update fails (or any subsequent code throws), the first update is rolled back and both accounts keep their original balances.

The lambda's return value is returned by execute:

JavaScript
var result = Db.Transaction.execute(function() {
  // ... writes ...
  return "success";
});

From Java

Java
String res = Db.Transaction.execute(() -> {
   Persistable from = type.build(a -> a.id("acct_1")).get();
   Persistable to   = type.build(a -> a.id("acct_2")).get();

   from.withField("balance", (long) from.fieldValue("balance") - amount).update();
   to.withField("balance",   (long) to.fieldValue("balance")   + amount).update();

   return "success";
});

Transaction spec

Pass a Db.Transaction.Spec to tune isolation, read-only, and deferrable modes:

JavaScript
var spec = Db.Transaction.Spec.make({
  isolationLevel: Db.Transaction.IsolationLevelEnum.REPEATABLE_READ,
  readOnly: false,
  deferrable: false
});

Db.Transaction.execute(spec, function() {
  // ...
});

Isolation levels

Values of Db.Transaction.IsolationLevelEnum map directly to PostgreSQL's SET TRANSACTION:

ValueMeaning
READ_COMMITTEDDefault. Each statement sees rows committed before it started.
REPEATABLE_READThe whole transaction sees a single snapshot; detects write conflicts.
SERIALIZABLETransactions appear to run one at a time; may cause serialization errors to retry.
READ_UNCOMMITTEDAccepted but treated as READ_COMMITTED by PostgreSQL.

Read-only and deferrable

  • readOnly: true — issues SET TRANSACTION READ ONLY. Any write throws "cannot execute UPDATE in a read-only transaction" (PostgreSQL only; not supported by H2).
  • deferrable: true — only takes effect when both readOnly is true and isolationLevel is SERIALIZABLE. Allows the transaction to wait for a safe snapshot instead of failing with serialization errors.

Checking whether you are in a transaction

Db.Transaction#isInTxn returns true when the current action is executing inside execute:

JavaScript
if (Db.Transaction.isInTxn()) {
  // take a different path, skip async dispatches, etc.
}

Internally, Db.Transaction stores its connection, invalidator, and action on the action-chain custom context (userTransactionConn, userTransactionInvalidatorUtil, userTransactionAction). Nested invocations detect this and throw.

Behavior of writes inside a transaction

When a write runs inside Db.Transaction.execute:

  1. It reuses the transaction's single JdbcConnection instead of starting its own SQL transaction.
  2. It shares the transaction's InvalidatorUtil, so invalidation queue entries for all inner writes are batched together in initial state.
  3. forceAsyncCalcFieldRefresh is forced to true, so stored calc fields are always refreshed via the invalidation queue (never inline).
  4. Per-operation retry is disabled — any SQL error aborts the whole transaction.
  5. After the outer execute commits, all batched invalidations are promoted to pending in a single call to commitInvalidatedQueues. Only then do after* / async callbacks fire.

If execute throws, the platform:

  • Calls conn.endTransaction(false) to roll back the SQL changes.
  • Calls abortInvalidatedQueues to discard the initial invalidation entries, so nothing stale is processed.
  • Releases the connection.

See Entity Write Transaction Model for the full write pipeline view.

Restrictions

These are enforced at runtime and throw a C3RuntimeException if violated.

RestrictionError message
No nested Db.Transaction.execute calls.Already in a Db.Transaction!
Only the sql datastore participates. Accessing kv or external datastores throws.Can't use datastore '<name>' in Db.Transaction
Types must be in the default Db.Domain. Types with @db(domain=...) throw.Can't use domain '<name>' in Db.Transaction
Cached types cannot be accessed, because cache invalidation cannot work atomically with the transaction.Can't access Cached type '<name>' in Db.Transaction
You cannot submit an AsyncAction.Can't submit AsyncAction while in transaction.
You cannot start a BatchJob / MapReduce job.Can't start '<type>' job while in transaction.
You cannot start a Workflow.Can't start workflow while in transaction.

Exceptions to the domain rule

Types whose domain is "system" (or "queues") can be read/written inside the transaction — they simply don't participate in the SQL transaction. This is how the platform itself reads configuration and writes queue entries during your transaction.

Retry policy

There is no internal retry. Any SQL error (serialization failure, deadlock, connection drop, unique-index violation, etc.) rolls the whole transaction back. If you want retry semantics (e.g. for SERIALIZABLE conflicts), implement them outside the execute call:

JavaScript
function withRetry(maxAttempts, fn) {
  for (var i = 0; i < maxAttempts; i++) {
    try { return fn(); }
    catch (e) {
      if (i === maxAttempts - 1) throw e;
      if (!isSerializationFailure(e)) throw e;
    }
  }
}

withRetry(3, function() {
  return Db.Transaction.execute(spec, function() { /* ... */ });
});

Not suitable for async / job contexts

Db.Transaction is a request-scoped, single-connection construct. Do not call it from inside a BatchJob, MapReduce, Workflow, or AsyncAction processor entry point — it is appropriate for the synchronous request that schedules work, not for the async body of that work.

Worked example: balance transfer

Java
@Test
public void balanceTransfer() {
   Persistable.Subtype type = C3.persistable("Account");
   Persistable acct1 = type.build(a -> a.id("acct_1").v("balance", 1000)).create();
   Persistable acct2 = type.build(a -> a.id("acct_2").v("balance", 0)).create();

   // Successful transfer — both updates commit atomically.
   Db.Transaction.execute(() -> transfer(acct1.id(), acct2.id(), 100L));
   assertEquals(900L, acct1.get().fieldValue("balance"));
   assertEquals(100L, acct2.get().fieldValue("balance"));

   // Overdraft — check runs after the debit/credit updates. When it throws,
   // BOTH updates are rolled back.
   try {
      Db.Transaction.execute(() -> transfer(acct1.id(), acct2.id(), 1000L));
      fail();
   } catch (C3RuntimeException t) {
      assertTrue(t.getLocalizedMessage().contains("too poor"));
   }

   assertEquals(900L, acct1.get().fieldValue("balance"));  // unchanged
   assertEquals(100L, acct2.get().fieldValue("balance"));  // unchanged
}

private Supplier<Object> transfer(String fromId, String toId, long amount) {
   return () -> {
      Persistable.Subtype t = C3.persistable("Account");
      Persistable from = t.build(a -> a.id(fromId)).get();
      Persistable to   = t.build(a -> a.id(toId)).get();

      long fromBalance = from.fieldValue("balance");
      long toBalance   = to.fieldValue("balance");

      from.withField("balance", fromBalance - amount).update();
      to.withField("balance",   toBalance   + amount).update();

      // Check after the writes to prove rollback works.
      if (amount > fromBalance)
         throw Err.formatted("You are too poor for this transfer!");

      return "success";
   };
}

From Db_Transaction_Test.balanceTransfer.

Type reference

Db.Transaction

Text
type Db.Transaction mixes Value {
  execute: function(action: !lambda(): any): any
  execute: function(txnSpec: !Db.Transaction.Spec, action: !lambda(): any): any
  isInTxn: inline function(): boolean
}

Db.Transaction.Spec

Text
type Db.Transaction.Spec mixes Spec {
  isolationLevel: !string enum Db.Transaction.IsolationLevelEnum = READ_COMMITTED
  readOnly:       boolean
  deferrable:     boolean
}

Db.Transaction.IsolationLevelEnum

Text
enum type Db.Transaction.IsolationLevelEnum {
  SERIALIZABLE
  REPEATABLE_READ
  READ_COMMITTED
  READ_UNCOMMITTED
}

See also

Was this page helpful?