What happens at the database level during a…

A transaction combines multiple database operations into a single atomic block. At the DBMS level, this ensures:

Atomicity: All operations within a transaction are either fully completed or rolled back. If any operation fails, the entire transaction is canceled, and the database is restored to its state before the transaction began.
Consistency: The transaction transitions the database from one consistent state to another. The DBMS ensures integrity constraints (primary keys, foreign keys, unique constraints, etc.) are maintained during the transaction.
Isolation: Transactions are isolated from each other. Changes made by one transaction are invisible to others until the first is successfully completed (committed). Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) define how strictly this rule is enforced.
Durability: After a transaction successfully completes (commits), its changes are permanent and will not be lost even in case of system failure. The DBMS uses transaction logging (write-ahead logging) to ensure durability.

During a transaction, the DBMS:

Begins collecting changes in a temporary buffer or transaction log.
Applies locks to the affected data to prevent other transactions from modifying them simultaneously and to avoid conflicts (dirty reads, non-repeatable reads, phantom reads). Lock types (shared, exclusive) depend on the operations performed (read, write).
Logs all transaction operations in the transaction log before applying them to the main data storage. This allows rolling back the transaction if needed.
Upon successful completion of all operations, data from the temporary buffer or transaction log is written to the main data files (commit) and/or commit records are written to the log, ensuring durability.
In case of an error or explicit rollback, the DBMS uses information from the transaction log to undo all changes made by the transaction, restoring the database to its original (pre-transaction) state.
Releases locks on affected data, allowing other transactions to access them.

Example in Ruby on Rails:

# Begins a transaction
ActiveRecord::Base.transaction do
  # Operation 1
  user = User.create!(name: 'Test')

  # Operation 2
  profile = user.create_profile!(age: 30)

  # If an error occurs here (e.g., validation), the entire transaction will be rolled back
  # If all succeed, the transaction will be committed at the end of the block
end # End of transaction (automatic commit or rollback)

In case of explicit transaction management:

# Begins a transaction
ActiveRecord::Base.transaction do
  user = User.create!(name: 'Test')
  profile = user.create_profile!(age: 30)
  # Explicit commit (usually not needed when using a block)
  # ActiveRecord::Base.connection.commit_db_transaction
rescue
  # Explicit rollback in case of error (usually not needed when using a block)
  # ActiveRecord::Base.connection.rollback_db_transaction
end

What happens at the database level during a transaction?