Transactions

an5 ORM supports transactions to ensure data consistency when performing multiple related operations.

Sqlite note: SQLite uses file-level locking with BEGIN IMMEDIATE to avoid write deadlocks. In-memory databases support transactions within the connection lifetime.

Basic Transaction (Scoped)

An5DbContext.transaction commits when the callback returns and rolls back when it throws:

User user = db.transaction(tx -> {
  User created = db.getUser().create(
      new User().withEmail("john@example.com").withName("John"));
  db.getOrder().create(
      new Order().withUserId(created.getId()).withTotal(100));
  return created;
});

The work runs on the context’s adapter, so the typed handles take part in the same transaction; tx is that adapter again when you need raw SQL. Nested transaction() calls are rejected rather than flattened, because an inner commit would turn an outer rollback into a partial save.

Error Handling and Rollback

try {
  db.transaction(tx -> {
    db.getUser().create(new User().withEmail("john@example.com").withName("First"));
    throw new IllegalStateException("rollback");
  });
} catch (IllegalStateException expected) {
  // the insert above was discarded with the transaction
} catch (SQLException error) {
  // connection-level failure
}

Best Practices

  1. Keep transactions short - Minimize the time table rows are locked to avoid blocking concurrent queries.
  2. Handle errors properly - Always handle potential deadlock or timeout exceptions in application code.
  3. Consistent ordering - Always update tables and records in a deterministic order across all endpoints to prevent database deadlocks.
  4. Choose the appropriate isolation level - Tailor the transaction isolation according to concurrency and consistency requirements.

Next Steps