setTransactionIsolation method

Future<void> setTransactionIsolation(
  1. TransactionIsolation isolation
)

Set the transaction isolation level for the next transaction.

The isolation level determines how transactions interact with each other and what types of phenomena (dirty reads, non-repeatable reads, phantom reads) are prevented.

Oracle Isolation Levels:

  • READ COMMITTED (default): Prevents dirty reads. Other transactions can modify data between reads in your transaction.
  • SERIALIZABLE: Highest isolation. Transactions execute as if they were running one after another. Prevents dirty reads, non-repeatable reads, and phantom reads.
  • READ ONLY: Transaction can only read data, no DML allowed.

Parameters:

  • isolation: The isolation level to set

Example - Read Committed (Default):

await connection.setTransactionIsolation(TransactionIsolation.readCommitted);

await connection.executeUpdate('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
// Other transactions can see this change only after commit
await connection.commit();

Example - Serializable (Strictest):

await connection.setTransactionIsolation(TransactionIsolation.serializable);

// Start transaction
final balance1 = await connection.query('SELECT balance FROM accounts WHERE id = 1');

// Even if another transaction modifies this row and commits,
// this transaction will see the same data (snapshot isolation)
final balance2 = await connection.query('SELECT balance FROM accounts WHERE id = 1');

// balance1 == balance2 (repeatable read guaranteed)
await connection.commit();

Example - Read Only:

await connection.setTransactionIsolation(TransactionIsolation.readOnly);

// This is OK
final users = await connection.query('SELECT * FROM users');

// This will fail - DML not allowed in READ ONLY transaction
// await connection.executeUpdate('DELETE FROM users WHERE id = 1');

await connection.commit();

Note: The isolation level applies to the NEXT transaction, not the current one. Call this before starting your transaction logic.

Performance Impact:

  • READ COMMITTED: Best performance, lowest isolation
  • SERIALIZABLE: May cause more rollbacks due to conflicts, but ensures consistency
  • READ ONLY: Good performance, useful for reporting queries

See also:

  • commit to complete the transaction
  • rollback to cancel the transaction

Implementation

Future<void> setTransactionIsolation(TransactionIsolation isolation) async {
  _ensureConnected();

  String sql;
  switch (isolation) {
    case TransactionIsolation.readCommitted:
      sql = 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED';
      break;
    case TransactionIsolation.serializable:
      sql = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE';
      break;
    case TransactionIsolation.readOnly:
      sql = 'SET TRANSACTION READ ONLY';
      break;
  }

  // Execute the SET TRANSACTION command
  final stmt = await execute(sql);
  await stmt.close();
}