Transactions and Isolation Levels

Overview

Transactions provide a mechanism for grouping database operations into atomic units that either complete entirely or fail entirely, maintaining data integrity across multiple operations. A transaction represents a sequence of one or more SQL operations treated as a single logical unit of work. The concept originates from database management systems research in the 1970s, particularly the work on System R at IBM.

The transaction model addresses the fundamental challenge of concurrent access to shared data. When multiple processes or threads access the same database simultaneously, without proper coordination, operations can interleave in ways that corrupt data or produce inconsistent results. Transactions solve this by providing guarantees about how operations interact with each other and with system failures.

Isolation levels define the degree to which concurrent transactions are isolated from each other. Different isolation levels offer different trade-offs between consistency guarantees and system performance. A stricter isolation level prevents more anomalies but typically reduces concurrency and throughput. The SQL standard defines four isolation levels, though not all databases implement all levels.

# Basic transaction in Ruby with ActiveRecord
ActiveRecord::Base.transaction do
  account1.update!(balance: account1.balance - 100)
  account2.update!(balance: account2.balance + 100)
end
# Both updates succeed or both roll back

Transactions matter in any system where data consistency is required across multiple operations. Financial applications, inventory management, booking systems, and any domain where race conditions could corrupt data require proper transaction management. The choice of isolation level significantly impacts both correctness and performance characteristics of the application.

Key Principles

Transactions follow the ACID properties: Atomicity, Consistency, Isolation, and Durability. Atomicity guarantees that all operations within a transaction complete successfully or none do. If any operation fails, the database rolls back all changes made by the transaction, leaving the database in its original state. This prevents partial updates that could leave data in an inconsistent state.

Consistency ensures that a transaction transforms the database from one valid state to another valid state. The database enforces constraints, triggers, and cascades during transaction execution. If a transaction would violate any integrity constraint, the entire transaction fails and rolls back. This preserves business rules and data invariants encoded in the database schema.

Isolation determines how transaction integrity is visible to other concurrent transactions. This is where isolation levels come into play. Perfect isolation would mean that concurrent transactions execute as if they were serial, one after another. However, perfect isolation severely limits concurrency. Isolation levels relax certain guarantees to improve performance while still preventing specific types of anomalies.

Durability guarantees that once a transaction commits, its changes persist even in the face of system failures. Committed data survives power losses, crashes, and other catastrophic events. Database systems typically achieve durability through write-ahead logging, where changes are written to a persistent log before being applied to the main database files.

The SQL standard defines four isolation levels, each preventing different read phenomena:

Read Uncommitted allows transactions to read uncommitted changes from other transactions. This is the weakest isolation level and permits dirty reads, non-repeatable reads, and phantom reads. Most applications avoid this level due to the risk of reading data that will be rolled back.

Read Committed ensures transactions only read committed data. This prevents dirty reads but allows non-repeatable reads and phantom reads. A transaction at this level might read different values for the same row at different times if another transaction commits changes between reads. This is the default isolation level for PostgreSQL and many other databases.

Repeatable Read guarantees that if a transaction reads a row, subsequent reads within the same transaction will return the same values. This prevents dirty reads and non-repeatable reads but allows phantom reads. A transaction might see new rows that match its search criteria appear between reads, but existing rows remain consistent.

Serializable provides the strictest isolation by ensuring transactions execute as if they were run serially. This prevents all read phenomena: dirty reads, non-repeatable reads, and phantom reads. Serializable isolation typically involves locking or optimistic concurrency control mechanisms that detect conflicts and abort transactions that would violate serializability.

Three primary read phenomena illustrate why isolation levels matter:

A dirty read occurs when a transaction reads data written by a concurrent uncommitted transaction. If that transaction rolls back, the reading transaction has seen data that never existed in any committed state. This can lead to cascading failures and incorrect business logic execution.

A non-repeatable read happens when a transaction reads the same row twice and gets different values because another transaction modified and committed changes between the reads. This violates the expectation that data remains stable within a transaction.

A phantom read occurs when a transaction executes the same query twice and gets different sets of rows. Another transaction inserted or deleted rows that match the query criteria between executions. This differs from a non-repeatable read in that it involves the appearance or disappearance of entire rows rather than changes to existing rows.

Ruby Implementation

Ruby applications typically interact with transactions through database adapter libraries. ActiveRecord, the ORM included with Rails, provides a high-level transaction API. The transaction method wraps a block of code in a database transaction, automatically committing on success or rolling back on exceptions.

# ActiveRecord transaction with explicit exception handling
User.transaction do
  user = User.create!(name: "Alice", email: "alice@example.com")
  Account.create!(user: user, balance: 1000)
  AuditLog.create!(action: "user_created", user_id: user.id)
end
# All three records created or none created

ActiveRecord transactions propagate exceptions to trigger rollbacks. Any exception raised within the transaction block causes a rollback. The exception then propagates up the call stack unless caught. This behavior encourages using exceptions for flow control in transaction contexts.

# Transaction with explicit rollback
User.transaction do
  user = User.create!(name: "Bob")
  
  if user.email.blank?
    raise ActiveRecord::Rollback
  end
  
  Account.create!(user: user)
end
# ActiveRecord::Rollback is caught by transaction and doesn't propagate

ActiveRecord::Rollback is a special exception that triggers a rollback without propagating outside the transaction block. This allows conditional rollback logic without affecting error handling in calling code.

Nested transactions in ActiveRecord use savepoints rather than true nested transactions. When a transaction block is nested inside another transaction block, ActiveRecord creates a savepoint before the inner block and rolls back to that savepoint if the inner transaction fails.

# Nested transaction using savepoints
User.transaction do
  user = User.create!(name: "Charlie")
  
  begin
    User.transaction(requires_new: true) do
      # This creates a savepoint
      user.update!(email: "invalid-email")
    end
  rescue ActiveRecord::RecordInvalid
    # Inner transaction rolled back to savepoint
    # Outer transaction continues
  end
  
  Account.create!(user: user)
end

The requires_new option forces creation of a savepoint even when already inside a transaction. Without this option, nested transaction blocks simply execute within the existing transaction without creating savepoints.

Setting isolation levels in ActiveRecord depends on the database adapter. PostgreSQL and MySQL adapters support isolation level configuration through adapter-specific methods.

# Setting isolation level in ActiveRecord (PostgreSQL)
ActiveRecord::Base.transaction(isolation: :serializable) do
  account = Account.lock.find(account_id)
  account.balance += 100
  account.save!
end

The isolation parameter accepts symbols corresponding to standard isolation levels: :read_uncommitted, :read_committed, :repeatable_read, and :serializable. Not all databases support all levels, and behavior varies by database system.

The Sequel gem provides similar transaction functionality with a slightly different API:

# Sequel transaction
DB.transaction do
  DB[:users].insert(name: "Dave")
  DB[:accounts].insert(user_id: DB[:users].max(:id), balance: 500)
end

Sequel supports explicit savepoints with the savepoint method:

# Sequel savepoint
DB.transaction do
  DB[:users].insert(name: "Eve")
  
  DB.transaction(savepoint: true) do
    DB[:audit_log].insert(action: "user_created")
  end
end

For applications using the pg gem directly without an ORM, transaction management requires explicit SQL:

# Raw transaction with pg gem
conn = PG.connect(dbname: 'myapp')

begin
  conn.exec("BEGIN")
  conn.exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
  
  result = conn.exec_params(
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
    [100, account1_id]
  )
  
  conn.exec_params(
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
    [100, account2_id]
  )
  
  conn.exec("COMMIT")
rescue PG::Error => e
  conn.exec("ROLLBACK") rescue nil
  raise
ensure
  conn.close
end

This approach provides fine-grained control but requires explicit error handling and transaction lifecycle management.

Implementation Approaches

Pessimistic Locking acquires locks on database rows to prevent concurrent modifications. When a transaction reads a row with a lock, other transactions must wait until the lock releases before accessing that row. This approach guarantees consistency but can create contention and reduce throughput.

# Pessimistic locking in ActiveRecord
Account.transaction do
  account = Account.lock.find(account_id)
  account.balance -= 100
  account.save!
end
# SELECT ... FOR UPDATE acquires row lock

The lock method generates a SELECT FOR UPDATE query, which acquires an exclusive lock on selected rows. Other transactions attempting to lock the same rows block until the first transaction commits or rolls back. Deadlocks can occur when transactions acquire locks in different orders.

Optimistic Locking assumes conflicts are rare and detects them after they occur. Each row includes a version column that increments on every update. When updating, the transaction checks that the version hasn't changed since the row was read. If the version changed, another transaction modified the row, and the update fails.

# Optimistic locking in ActiveRecord
# Requires lock_version column
class Account < ApplicationRecord
end

account = Account.find(account_id)
account.balance -= 100
account.save!
# Raises ActiveRecord::StaleObjectError if version changed

ActiveRecord automatically handles optimistic locking when a lock_version integer column exists in the table. The application must catch StaleObjectError and implement retry logic or notify the user of the conflict.

Application-Level Transaction Coordination manages distributed transactions across multiple databases or services. This requires implementing two-phase commit protocols or using eventual consistency patterns. Ruby applications often use message queues or event sourcing to coordinate distributed transactions.

# Saga pattern for distributed transactions
class TransferService
  def transfer(from_account_id, to_account_id, amount)
    withdrawal = withdraw(from_account_id, amount)
    
    begin
      deposit(to_account_id, amount)
    rescue => e
      compensate_withdrawal(withdrawal)
      raise
    end
  end
  
  private
  
  def withdraw(account_id, amount)
    Account.transaction do
      account = Account.find(account_id)
      account.balance -= amount
      account.save!
      Withdrawal.create!(account: account, amount: amount)
    end
  end
  
  def compensate_withdrawal(withdrawal)
    Account.transaction do
      account = withdrawal.account
      account.balance += withdrawal.amount
      account.save!
      withdrawal.update!(compensated: true)
    end
  end
end

The saga pattern breaks a distributed transaction into a sequence of local transactions, each with a compensating transaction that undoes its effects. If any step fails, compensating transactions execute in reverse order to maintain consistency.

Read-Only Transactions optimize read-heavy workloads by declaring transactions that only read data. Databases can optimize read-only transactions by avoiding write locks and using snapshot isolation more efficiently.

# Read-only transaction in PostgreSQL
ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute(
    "SET TRANSACTION READ ONLY"
  )
  
  accounts = Account.where(balance: ...100).to_a
  total = accounts.sum(&:balance)
end

Some databases allow setting a transaction as read-only at the start, enabling optimizations like reading from replicas or using more aggressive caching strategies.

Database-Specific Features provide advanced transaction capabilities beyond standard SQL. PostgreSQL supports serializable snapshot isolation (SSI), which provides full serializability with better performance than traditional two-phase locking. MySQL's InnoDB storage engine uses multi-version concurrency control (MVCC) to implement isolation levels efficiently.

Common Pitfalls

Deadlocks occur when two or more transactions wait for each other to release locks, creating a circular dependency that prevents progress. Database systems detect deadlocks and abort one transaction to break the cycle. Applications must handle deadlock exceptions and implement retry logic.

# Deadlock scenario
# Transaction 1
Account.transaction do
  account1 = Account.lock.find(1)
  sleep(0.1)  # Simulating work
  account2 = Account.lock.find(2)  # Deadlock here
end

# Transaction 2 (concurrent)
Account.transaction do
  account2 = Account.lock.find(2)
  sleep(0.1)
  account1 = Account.lock.find(1)  # Deadlock here
end

Preventing deadlocks requires acquiring locks in a consistent order across all transactions. Always lock resources in the same sequence, such as by ascending primary key value.

Lost Updates happen when two transactions read the same row, modify it, and write it back, with the second write overwriting the first. This occurs even at Read Committed isolation level because reads don't prevent concurrent modifications.

# Lost update example
# Initial balance: 1000

# Transaction 1
account = Account.find(1)  # Reads balance: 1000
account.balance += 100
account.save!  # Writes balance: 1100

# Transaction 2 (concurrent, starts before T1 commits)
account = Account.find(1)  # Reads balance: 1000
account.balance -= 50
account.save!  # Writes balance: 950 (lost the +100 update)

Preventing lost updates requires either pessimistic locking (SELECT FOR UPDATE) or optimistic locking with version checking. Repeatable Read isolation alone doesn't prevent lost updates because it only affects reads, not writes.

Long-Running Transactions hold locks or snapshot references for extended periods, blocking other transactions and consuming resources. Long transactions increase the likelihood of conflicts, deadlocks, and resource exhaustion.

# Problematic long transaction
User.transaction do
  users = User.all
  
  users.each do |user|
    # Expensive operation inside transaction
    external_api_call(user)  # Bad: Network I/O in transaction
    sleep(0.5)  # Simulating slow processing
    user.update!(last_processed: Time.now)
  end
end

Move expensive operations, network calls, and file I/O outside transactions. Keep transactions focused on the minimal set of database operations required for consistency.

Incorrect Exception Handling can cause transactions to commit when they should roll back. In ActiveRecord, only exceptions that propagate out of the transaction block trigger rollbacks. Catching and suppressing exceptions within the block allows the transaction to commit despite errors.

# Incorrect exception handling
User.transaction do
  user = User.create!(name: "Frank")
  
  begin
    Account.create!(user: user, balance: -1000)  # Fails validation
  rescue ActiveRecord::RecordInvalid
    # Exception caught, transaction continues and commits
    puts "Account creation failed"
  end
end
# User record committed without account

Let exceptions propagate out of the transaction block to trigger rollback, or explicitly raise ActiveRecord::Rollback after logging the error.

Serialization Failures at Serializable isolation level occur when the database detects conflicts that would violate serializability. Applications must catch these exceptions and retry the transaction.

# Handling serialization failures
MAX_RETRIES = 3

def transfer_with_retry(from_id, to_id, amount)
  retries = 0
  
  begin
    Account.transaction(isolation: :serializable) do
      from_account = Account.find(from_id)
      to_account = Account.find(to_id)
      
      from_account.balance -= amount
      to_account.balance += amount
      
      from_account.save!
      to_account.save!
    end
  rescue ActiveRecord::SerializationFailure => e
    retries += 1
    retry if retries < MAX_RETRIES
    raise
  end
end

PostgreSQL's serializable isolation uses predicate locking and can abort transactions that would create serialization anomalies. Applications should implement exponential backoff when retrying failed transactions.

Insufficient Isolation allows data anomalies that violate business logic. Using Read Committed isolation when Repeatable Read is required can lead to inconsistent aggregations or reports.

# Insufficient isolation for accounting report
Account.transaction do
  total_deposits = Transaction.where(type: "deposit").sum(:amount)
  total_withdrawals = Transaction.where(type: "withdrawal").sum(:amount)
  
  # New transactions committed by other processes between queries
  # Totals may not reflect a consistent snapshot
  
  Report.create!(
    deposits: total_deposits,
    withdrawals: total_withdrawals,
    net: total_deposits - total_withdrawals
  )
end

Use Repeatable Read or Serializable isolation for reports or operations that require a consistent view of data across multiple queries.

Performance Considerations

Isolation level selection directly impacts database performance and scalability. Stricter isolation levels provide stronger consistency guarantees but reduce concurrency by holding locks longer or detecting more conflicts. The choice depends on the specific consistency requirements of each transaction.

Read Committed offers the best performance for most workloads. This isolation level allows high concurrency because it holds locks only for the duration of individual statements, not the entire transaction. Reads don't block writes, and writes don't block reads (except for the specific row being written). Most web applications use Read Committed as the default isolation level.

Repeatable Read degrades performance compared to Read Committed because it must maintain a consistent snapshot of data throughout the transaction. In MVCC databases like PostgreSQL, this means keeping older versions of rows that might be needed for currently running transactions. This increases storage requirements and can bloat tables if long-running transactions prevent old versions from being cleaned up.

# Performance comparison of isolation levels
require 'benchmark'

Benchmark.bm do |x|
  x.report("read_committed:") do
    1000.times do
      Account.transaction(isolation: :read_committed) do
        Account.where("balance > ?", 100).count
      end
    end
  end
  
  x.report("repeatable_read:") do
    1000.times do
      Account.transaction(isolation: :repeatable_read) do
        Account.where("balance > ?", 100).count
      end
    end
  end
  
  x.report("serializable:") do
    1000.times do |i|
      begin
        Account.transaction(isolation: :serializable) do
          Account.where("balance > ?", 100).count
        end
      rescue ActiveRecord::SerializationFailure
        # Serializable may abort transactions
      end
    end
  end
end

Serializable isolation has the highest performance cost. Traditional two-phase locking implementations acquire many locks and hold them for the entire transaction duration, severely limiting concurrency. PostgreSQL's serializable snapshot isolation performs better by detecting conflicts without explicit locking, but still aborts transactions when conflicts are detected, requiring application-level retry logic.

Lock Contention increases with stricter isolation levels and pessimistic locking. When multiple transactions compete for locks on the same rows, they must wait in sequence rather than executing concurrently. This serializes execution and reduces throughput.

# Lock contention measurement
start_time = Time.now
threads = 10.times.map do
  Thread.new do
    Account.transaction do
      account = Account.lock.find(1)  # All threads contend for same row
      account.balance += 1
      account.save!
    end
  end
end
threads.each(&:join)
elapsed = Time.now - start_time
# Higher elapsed time indicates more lock contention

Reducing lock contention requires minimizing the time locks are held and reducing the number of transactions that access the same resources concurrently. Partition data to avoid hot spots where many transactions access the same rows.

Connection Pool Exhaustion occurs when transactions hold database connections for extended periods, preventing other requests from acquiring connections. Long-running transactions at higher isolation levels exacerbate this problem.

# Connection pool configuration
ActiveRecord::Base.establish_connection(
  adapter: "postgresql",
  pool: 5,  # Number of concurrent connections
  checkout_timeout: 5  # Seconds to wait for available connection
)

# Each long transaction blocks a connection
Account.transaction do
  Account.all.each do |account|
    # This holds a connection for a long time
    process_account(account)
  end
end

Keep transactions short and focused. Process large datasets in batches outside of transactions, or use multiple smaller transactions with savepoints.

Snapshot Maintenance in MVCC systems requires maintaining multiple versions of rows. Higher isolation levels keep snapshots longer, increasing storage overhead and slowing down queries that must check multiple row versions to find the correct visible version.

# Long transaction keeping old snapshots alive
Account.transaction(isolation: :repeatable_read) do
  initial_total = Account.sum(:balance)
  
  # Many updates happen in other transactions
  sleep(60)
  
  # This transaction still sees the old snapshot
  # Database must maintain old row versions
  final_total = Account.sum(:balance)
end

Avoid long-running transactions at Repeatable Read or Serializable isolation. Use Read Committed for operations that can tolerate seeing committed changes from concurrent transactions.

Index Usage affects transaction performance significantly. Transactions that scan large numbers of rows hold locks longer and create more work for the MVCC system. Proper indexing reduces the number of rows accessed and the duration of lock holds.

# Without index: full table scan, many row locks
Account.transaction do
  accounts = Account.where("email LIKE ?", "%@example.com%")
  accounts.update_all(verified: true)
end

# With index on email: fewer rows accessed, faster execution
# CREATE INDEX idx_accounts_email ON accounts(email);

Transaction performance optimization requires balancing consistency requirements against concurrency needs. Profile transaction execution times, measure lock wait times, and adjust isolation levels and transaction scope based on actual application behavior.

Tools & Ecosystem

ActiveRecord is the default ORM for Ruby on Rails applications and provides the most commonly used transaction API in the Ruby ecosystem. ActiveRecord abstracts database-specific transaction behavior and provides a consistent interface across different database adapters (PostgreSQL, MySQL, SQLite, etc.).

ActiveRecord's transaction instrumentation integrates with Rails logging and monitoring tools. Transaction events appear in Rails logs with timing information, allowing developers to identify slow or problematic transactions.

# ActiveRecord with detailed logging
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.logger.level = Logger::DEBUG

User.transaction do
  # Logs show transaction BEGIN, queries, and COMMIT/ROLLBACK
  User.create!(name: "Grace")
end

Sequel is an alternative ORM that provides more direct SQL access and finer control over database operations. Sequel's transaction API is similar to ActiveRecord's but offers more flexibility for advanced use cases.

# Sequel transaction with manual rollback control
DB.transaction do
  DB[:accounts].where(id: account_id).update(balance: Sequel[:balance] - 100)
  
  # Explicit rollback
  raise Sequel::Rollback if some_condition
end

Sequel supports database-specific features more directly than ActiveRecord, making it a good choice for applications that need to use advanced database capabilities.

database_cleaner gem helps manage database state in test environments. It provides strategies for cleaning the database between tests while maintaining transactional test behavior when possible.

# database_cleaner configuration
RSpec.configure do |config|
  config.before(:suite) do
    DatabaseCleaner.strategy = :transaction
    DatabaseCleaner.clean_with(:truncation)
  end
  
  config.around(:each) do |example|
    DatabaseCleaner.cleaning do
      example.run
    end
  end
end

The transaction strategy wraps each test in a transaction that rolls back after the test completes, providing fast test cleanup without actually deleting data.

pg gem provides direct PostgreSQL access for Ruby applications. When using pg without an ORM, transaction management requires explicit SQL commands but offers maximum control over transaction behavior.

# Advanced transaction control with pg gem
conn = PG.connect(dbname: 'myapp')

conn.transaction do |c|
  c.exec("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE")
  
  # Query runs on a consistent snapshot, potentially waiting for a
  # suitable snapshot to become available (DEFERRABLE)
  result = c.exec("SELECT * FROM accounts")
end

mysql2 gem serves a similar role for MySQL databases, providing low-level access to MySQL-specific transaction features.

Apartment gem manages multi-tenant databases with separate schemas or databases per tenant. It handles transaction propagation across tenant contexts.

# Apartment transactions across tenants
Apartment::Tenant.switch!('tenant1')

Account.transaction do
  account = Account.find(account_id)
  account.balance -= 100
  account.save!
  
  # Switch tenant within transaction
  Apartment::Tenant.switch!('tenant2')
  AuditLog.create!(action: "transfer", amount: 100)
end

Makara gem provides database connection pooling with master/replica awareness. It routes write transactions to master databases and read-only queries to replicas.

# Makara configuration
production:
  adapter: postgresql_makara
  makara:
    master_ttl: 5
    blacklist_duration: 5
    connections:
      - role: master
        host: master.db.example.com
      - role: slave
        host: replica1.db.example.com
      - role: slave
        host: replica2.db.example.com

Makara automatically sends transactions to the master database while distributing read-only queries across replicas, improving performance and scalability.

ActiveRecord Import gem provides bulk insert capabilities that work within transactions. This is useful for importing large datasets while maintaining transactional safety.

# Bulk import within transaction
User.transaction do
  users = 1000.times.map { |i| User.new(name: "User#{i}") }
  User.import users, validate: false
  
  # All 1000 users inserted or none
end

Reference

Isolation Levels

ActiveRecord Transaction Methods

Common Transaction Patterns

PostgreSQL Transaction Commands

Exception Types

Transaction Configuration Options

Performance Metrics to Monitor