Transaction

Why Trust Techopedia

What Is a Transaction?

A transaction is a sequence of related database operations that a database management system (DBMS) treats as a single unit of work. Transactions ensure that designated groups of operations are executed together. This is important in scenarios where multiple steps are dependent on each other.

Advertisements

Database transactions need to comply with four important properties: atomicity, consistency, isolation, and durability (ACID). The goal is to make sure the database is never left in an inconsistent state.

  • Atomicity guarantees each transaction either succeeds or fails completely.
  • Consistency ensures that transactions follow both the DBMS’ built-in rules and any business rules implemented by developers.
  • Isolation prevents transactions in progress from interacting with each other.
  • Durability ensures that committed transactions are saved permanently.

Key Takeaways

  • Transactions support database integrity by ensuring interdependent operations are executed as a single unit of work.
  • Database transactions need to comply with four important principles: atomicity, consistency, isolation, and durability.
  • Transactions have a lifecycle that’s defined by four states: begin, execute, commit, and rollback.
  • Application developers can specify which operations should be grouped together as a transaction.
  • Transactions are important in use cases like online banking and reservation systems, where even minor errors can have significant consequences.

What Is a Transactional Database?

The term “transactional database” can be used to describe any database that fully complies with ACID principles.

Transactional databases ensure that when multiple operations are performed together, they are treated as a single unit of work, and either all of them succeed or none of them do. This prevents partial updates or inconsistencies that could corrupt the data.

Most relational databases are ACID-compliant because rows and columns provide a strong framework for defining and enforcing rules (consistency), tracking changes (atomicity and durability), and managing concurrent access (isolation).

Some other types of databases are not as well-suited. For example, Redis is an in-memory key-value store designed for speed and high availability. Redis supports atomic operations, but it doesn’t guarantee durability. If the Redis server crashes or restarts unexpectedly, there’s a potential for data loss, especially for recent writes that haven’t been backed up.

How Database Transactions Work

Software application developers program which operation sequences should be grouped together as a transaction in source code and then the DBMS handles the execution of commands.

If every operation in a transaction executes as expected, the DBMS confirms the transaction, and changes are committed (recorded in the database). If even one operation fails, however, the entire transaction is rolled back to prevent inconsistencies, and no changes are made to the database.

Transactions use locks, timestamps, and other mechanisms to manage access to data and ensure concurrent operations can’t interfere with each other.

It should be noted that some DBMSes allow developers to enable auto-commit within their application code, which allows each operation to be treated like it’s a transaction. If a developer uses a DBMS with auto-commit enabled by default, they will need to disable it in code before defining transaction boundaries.

How Developers Define Transactions

Software developers define database transactions by grouping multiple database operations together. To transfer $100 between two linked bank accounts, for example, the transaction’s instructions might look like this:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance – 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Life Cycle of a Transaction

Transactions have a lifecycle that’s defined by four states: Begin, execute, commit, and rollback.

  • Begin marks the start of the transaction.
  • Execute performs the database operations (DML commands).
  • Commit finalizes the transaction and makes all changes permanent.
  • Rollback undoes all operations if an error occurs and restores the database to its initial state.

4 Transaction Types

Different types of database transactions can be categorized based on their purpose, scope, and how they are executed.

Here are four different types of transactions that are commonly used:

Single-statement transactions
Individual SQL statements that are treated as transactions.
Distributed transactions
Transactions that span multiple databases systems. Distributed transactions often use a two-phase commit (2PC) protocol to ensure consistency across all systems.
Nested transactions
Structured hierarchically, with sub-transactions nested within a parent transaction. If a sub-transaction fails, its changes can be rolled back independently of the parent.
Pessimistic transactions
Lock resources at various isolation levels to prevent conflicts. Locks prevent other transactions from accessing or modifying the same data until the transaction is committed. Different isolation levels can be used to control how strict the locking is.

Transaction Use Cases

When even minor data inconsistencies or errors can have significant consequences, ACID-compliant transactions provide a reliable mechanism for ensuring data integrity, consistency, and reliability.

Common use cases include:

  • Financial Systems
  • E-commerce
  • Healthcare
  • Government and legal systems
  • Manufacturing and logistics
  • Telecommunications
  • Retail
  • Education
  • Transportation
  • Utilities
  • Supply chain
  • Gaming
  • Fraud detection

Transaction Pros & Cons

Executing related database operations as a single transaction has its benefits, but it also comes with trade-offs:

Pros

  • Can be used to make application logic simpler
  • Help ensure the database always remains in a consistent state
  • If something goes wrong, a transaction can be rolled back to its initial state and prevent partial changes
  • Transaction isolation mechanisms allow concurrent users to use the database without conflicts
  • Once a transaction is committed, changes are permanent

Cons

  • Transaction overhead can slow performance issues when many transactions run at the same time
  • Distributed transactions across multiple systems need complex coordination protocols, which is hard to manage
  • High isolation levels can temporarily block other operations and reduce throughput
  • Poorly designed isolation levels can potentially compromise data integrity by creating issues like phantom reads

The Bottom Line

A transaction, by definition, is a single action. Database transactions are groups of related tasks that are treated as a single action.

Database transactions use an all-or-nothing approach: either all the operations within the transaction are committed to the database, or none of the changes are applied.

FAQs

What is a transaction in simple terms?

What does a DB transaction do?

What is an example of a transactional database?

What is a database transaction vs. an operation?

Advertisements

Related Terms

Margaret Rouse
Technology Expert
Margaret Rouse
Technology Expert

Margaret is an award-winning technical writer and teacher known for her ability to explain complex technical subjects to a non-technical business audience. Over the past twenty years, her IT definitions have been published by Que in an encyclopedia of technology terms and cited in articles by the New York Times, Time Magazine, USA Today, ZDNet, PC Magazine, and Discovery Magazine. She joined Techopedia in 2011. Margaret's idea of a fun day is helping IT and business professionals learn to speak each other’s highly specialized languages.