Transactions

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

Mysql note: MySQL (InnoDB engine) enforces full ACID compliance and row-level locking during active transactions.

Basic Transaction (Scoped)

val user = db.transaction {
    val created = db.user.create(User(email = "john@example.com", name = "John"))
    db.order.create(Order(userId = created.id, total = 100))
    created
}

The block returns the transaction’s value and commits when it returns. It is handed the An5 runtime for raw SQL, but the typed handles on db share the same connection, so they are part of the transaction too. Nested transaction calls are rejected rather than flattened.

Error Handling and Rollback

try {
    db.transaction<Unit> {
        db.user.create(User(email = "john@example.com", name = "First"))
        throw IllegalStateException("rollback")
    }
} catch (expected: IllegalStateException) {
    // the insert above was discarded with the transaction
}

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