Pyranid Logo

Core Concepts

Transactions

Transactions are a critical part of any DBMS - they allow multiple discrete operations to be combined into atomic units of work. While a transaction is in-progress, any work it performs is not visible to concurrent transactions until the transaction has successfully completed. In SQL, transactions are performed via BEGIN and COMMIT commands.

In Pyranid, the default behavior is for data access operations to be performed as if autocommit=true were set on the session - there is no transaction, all state changes are performed immediately.

However, any code that runs inside of the Database::transaction(TransactionalOperation) closure operates within the context of a transaction - no state changes will propagate until closure finishes its execution non-exceptionally.

database.transaction(() -> {
  // Anything that happens in here is part of the transaction.
  // If an exception bubbles out, the transaction will be rolled back.
});

All data access and manipulation APIs - Database::queryForObject, Database::execute(String, Object...) and friends - detect the presence of a transaction on the current thread of execution and automatically participate. This is usually the behavior you want. See the Context section below for more information on the mechanics, and for how to get a handle to the current transaction in order to manipulate it (e.g. creating a java.sql.Savepoint).

While less common, it is sometimes useful to share a transaction across multiple threads of execution. The Sharing Across Threads section below describes this functionality in more detail.

For performance reasons, no java.sql.Connection is fetched from the javax.sql.DataSource until the first data access operation occurs.

Basics

The canonical example - a bank transfer between two accounts.

database.transaction(() -> {
  // Pull initial account balances
  BigDecimal balance1 = database.queryForObject(
    "SELECT balance FROM account WHERE id=1", BigDecimal.class).get();
  BigDecimal balance2 = database.queryForObject(
    "SELECT balance FROM account WHERE id=2", BigDecimal.class).get();
  
  // Debit one and credit the other 
  balance1 = balance1.subtract(amount);
  balance2 = balance2.add(amount);

  // Persist changes
  database.execute("UPDATE account SET balance=? WHERE id=1", balance1);
  database.execute("UPDATE account SET balance=? WHERE id=2", balance2);
});

// For convenience, transactional operations may return values
Optional<BigDecimal> newBalance = database.transaction(() -> {
  // Make some changes
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");
  database.execute("UPDATE account SET balance=balance + 10 WHERE id=2");

  // Return the new value
  return database.queryForObject(
    "SELECT balance FROM account WHERE id=2", BigDecimal.class);
});

References:

Context

Internally, Database manages a thread-local stack of Transaction instances, so it can always provide a handle to the currenly-scoped transaction, if one exists.

The handle is useful for creating/restoring savepoints, marking as "force rollback", registering post-transaction operations, etc.

You cannot directly issue commits or rollbacks via Transaction. In order to provide a simple closure-based API, Pyranid performs those operations internally.

// Gets a handle to the current transaction, if any.
Optional<Transaction> transaction = database.currentTransaction();

// Output is "false"
out.println(transaction.isPresent());

database.transaction(() -> {
  // A transaction only exists for the life of the closure
  Optional<Transaction> actualTransaction = database.currentTransaction();

  // Output is "true"
  out.println(actualTransaction.isPresent());
});

References:

Sharing Across Threads

Should you need to share the same transaction across multiple threads, use the Database::participate(Transaction, TransactionalOperation) API.

database.transaction(() -> {
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");

  // Get a handle to the current transaction
  Transaction transaction = database.currentTransaction().get();

  new Thread(() -> {
    // In a different thread and participating in the existing transaction.
    // No commit or rollback will occur when the closure completes, but if an 
    // exception bubbles out the transaction will be marked as rollback-only
    database.participate(transaction, () -> {
      database.execute("UPDATE account SET balance=balance + 10 WHERE id=2");
    });
  }).run();

  // Wait a bit for the other thread to finish
  // (Don't do this in real systems)
  sleep(1000);
});

References:

Rolling Back

Rollbacks are performed by Pyranid in 3 scenarios:

  • An exception bubbles out of a transaction closure
  • You explicitly mark a transaction as "rollback only"
  • You create a savepoint and roll back to it
// Any exception that bubbles out will cause a rollback
database.transaction(() -> {
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");
  throw new IllegalStateException("Something's wrong!");
});

// You may mark a transaction as rollback-only, and it will roll back 
// after the closure execution has completed
database.transaction(() -> {
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");

  // Hmm...I changed my mind
  Transaction transaction = database.currentTransaction().get();
  transaction.setRollbackOnly(true);
});

// You may roll back to a savepoint
database.transaction(() -> {
  Transaction transaction = database.currentTransaction().get();
  Savepoint savepoint = transaction.createSavepoint();

  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");

  // Hmm...I changed my mind
  transaction.rollback(savepoint);
});

References:

Nesting

Each lexically-scoped "nested" transaction is independent of its enclosing transaction. There is no parent-child relationship.

However, if you would like this behavior, you may explicitly use the Database::participate(Transaction, TransactionalOperation) API to have a "child" participate in its parent transaction.

database.transaction(() -> {
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");

  // This transaction will commit
  database.transaction(() -> {
    database.execute("UPDATE account SET balance=balance + 10 WHERE id=2");
  });

  // This transaction will not!
  throw new IllegalStateException("I should not have used nested transactions here...");
});

Isolation

You may specify the normal DBMS isolation levels per-transaction as needed:

  • READ_COMMITTED
  • READ_UNCOMMITTED
  • REPEATABLE_READ
  • SERIALIZABLE
// If not specified, DEFAULT is assumed (whatever your DBMS prefers)
database.transaction(TransactionIsolation.SERIALIZABLE, () -> {
  database.execute("UPDATE account SET balance=balance - 10 WHERE id=1");
  database.execute("UPDATE account SET balance=balance + 10 WHERE id=2");
});

References:

Post-Transaction Operations

It is useful to be able to schedule code to run after a transaction has been fully committed or rolled back. Often, transaction management happens at a higher layer of code than business logic (e.g. a transaction-per-web-request pattern), so it is helpful to have a mechanism to "warp" local logic out to the higher layer.

Without this, you might run into subtle bugs like

  1. Write to database
  2. Send out notifications of system state change
  3. (Sometime later) transaction is rolled back
  4. Notification consumers are in an inconsistent state because they were notified of a change that was reversed by the rollback
// Business logic
class EmployeeService {
  public void giveEveryoneRaises() {
    database.execute("UPDATE employee SET salary=salary * 2");
    payrollSystem.startLengthyWarmupProcess();

    // Only send emails after the current transaction ends
    database.currentTransaction().get().addPostTransactionOperation((transactionResult) -> {
      if(transactionResult == TransactionResult.COMMITTED) {
        // Successful commit? email everyone with the good news
        for(Employee employee : findAllEmployees())
          sendCongratulationsEmail(employee);
      } else if(transactionResult == TransactionResult.ROLLED_BACK) {
        // Rolled back? We can clean up
        payrollSystem.cancelLengthyWarmupProcess();	
      }
    });
  }
	
  // Rest of implementation elided
}

// Servlet filter which wraps requests in transactions
class DatabaseTransactionFilter implements Filter {
  @Override
  public void doFilter(ServletRequest servletRequest,
                       ServletResponse servletResponse,
                       FilterChain filterChain) throws IOException, ServletException {
    database.transaction(() -> {
      // Above business logic would happen somewhere down the filter chain
      filterChain.doFilter(servletRequest, servletResponse);

      // Business logic has completed at this point but post-transaction
      // operations will not run until the closure exits
    });

    // By this point, post-transaction operations will have been run
  }

  // Rest of implementation elided
}
Previous
Statements
Next
ResultSet Mapping