Last modified: March 12, 2025

This article is written in: 🇺🇸

What Is a Transaction?

A database transaction is a sequence of operations performed as a single, indivisible unit of work. These operations—such as inserting, updating, or deleting records—are executed together to ensure data integrity and consistency, especially when multiple users or processes access the database at the same time.

1. Initial State:

┌───────────────────────┐             ┌───────────────────────┐
│      Account A        │             │      Account B        │
│                       │             │                       │
│     Balance: $100     │             │      Balance: $50     │
└───────────────────────┘             └───────────────────────┘

              │                                 │
              │                                 │
              ▼                                 ▼

2. Transaction Initiated:

┌───────────────────────────────────────────────────────────┐
│             Transferring $20 from Account A               │
│                         to Account B                      │
└───────────────────────────────────────────────────────────┘

              │                                 │
              ▼                                 ▼

3. After Transaction:

┌───────────────────────┐             ┌───────────────────────┐
│      Account A        │             │      Account B        │
│                       │             │                       │
│     Balance: $80      │             │      Balance: $70     │
└───────────────────────┘             └───────────────────────┘

In the example above, the transaction involves transferring $20 from Account A to Account B. If any part of this transaction fails—say, if the system crashes after debiting Account A but before crediting Account B—the transaction management system ensures that all changes are rolled back, returning the database to its initial state.

After reading the material, you should be able to answer the following questions:

1. What is a database transaction, and why is it important for maintaining data integrity and consistency in a database system?
2. What are the ACID properties of transactions, and how does each property (Atomicity, Consistency, Isolation, Durability) contribute to reliable transaction processing?
3. How does the post office analogy illustrate the principles of Atomicity, Consistency, Isolation, and Durability in database transactions?
4. What are the key components and operations involved in transaction management, including statements like Begin Transaction, Commit, and Rollback?
5. How do different concurrency control mechanisms, such as optimistic and pessimistic concurrency control, help manage simultaneous transactions and prevent conflicts in a database?

ACID Properties

Transactions in databases follow the ACID properties—Atomicity, Consistency, Isolation, and Durability—to ensure reliability, correctness, and robustness, even during errors or system failures.

Atomicity

Atomicity guarantees that a transaction is treated as a single, indivisible unit. Either all operations within the transaction succeed, or none do. If any operation within a transaction fails, all previously executed steps are reversed.

Transaction Example:

Initial State:
Account A: $100
Account B: $50

Transaction Steps:
1. Debit $20 from Account A (Account A: $80)
2. Credit $20 to Account B (Account B: $70)

If Step 2 fails:
Rollback Step 1 → Account A returns to $100

Atomicity prevents partial updates, preserving database consistency.

Consistency

Consistency ensures that transactions transition the database from one valid state to another valid state, following all rules, constraints, and triggers defined in the database.

Transaction Example:

Initial State:
Account A: $100
Account B: $50

After Transaction:
Account A: $80
Account B: $70

Total balance remains consistent:
Before: $150 | After: $150

Consistency maintains data integrity throughout transactions.

Isolation

Isolation ensures concurrent transactions operate independently, without affecting each other. Transactions run as if they are executed sequentially, preventing intermediate states from being visible to other concurrent transactions.

Isolation Example:

Transaction T1:
Reads Account A → Updates Account A

Transaction T2:
Reads Account B → Updates Account B

Even if T1 and T2 execute simultaneously:
T1 ↔ Account A (isolated)
T2 ↔ Account B (isolated)

No interference between transactions.

Isolation prevents transactions from causing conflicts or inconsistency.

Durability

Durability guarantees that once a transaction is committed, its effects are permanently saved, even if a system failure occurs immediately afterward. The committed state is stored on durable, non-volatile storage.

Durability Example:

Transaction Commit:
→ Changes saved permanently to disk.

System Crash Occurs:
→ Restart System

After Recovery:
→ Committed changes still present.

Durability ensures permanent recording of committed transactions.

Analogy of a post office

Once upon a time in a small village, there was a dedicated postman named Tom. Tom's job was to ensure all letters sent from the village post office reached their intended recipients safely and quickly.

One day, Tom received a special request. A villager named Alice wanted to send two important letters—one to her friend Bob and another to her cousin Charlie. Tom learned these letters were part of a surprise birthday celebration. Alice made it clear that both letters had to arrive together; otherwise, the surprise would be spoiled. Understanding this, Tom promised Alice that either both letters would be delivered or neither would leave the post office. This clearly demonstrated the principle of ATOMICITY, where tasks must fully complete or not occur at all.

The village post office had strict rules for handling letters: each must be stamped, sealed, and properly addressed. Tom carefully checked Alice's letters and found one missing a stamp. Knowing the importance of following the rules, he held both letters back until the issue was resolved. This careful approach ensured the postal service's reliability, highlighting CONSISTENCY—making sure every action follows set standards.

While Tom was working on Alice's letters, the post office was busy with other activities. Villagers like Dave were sending packages and receiving letters at the same time. Tom skillfully managed multiple tasks, ensuring each delivery was handled independently. Even though he was multitasking, Alice's letters and Dave's package were treated separately without interfering with each other. This demonstrated the principle of ISOLATION, where tasks carried out simultaneously do not affect each other negatively.

Eventually, Alice fixed the stamp issue. Once both letters were ready, Tom sent them out for delivery. Once dispatched, the action became permanent and couldn't be reversed. Tom recorded the details in the official logbook, ensuring clear documentation. Even if issues like bad weather or mechanical problems arose, the post office had ways to ensure Bob and Charlie would eventually receive their letters. This illustrated DURABILITY, meaning that once an action is complete, it stays permanent and secure, just like committed database transactions.

Transaction Management

Managing transactions involves coordinating their execution to uphold the ACID properties. This ensures that the database remains reliable and consistent, even when multiple transactions occur concurrently.

• The Begin Transaction statement initializes a transaction, signaling that subsequent operations should be treated as part of a single unit of work.
• A Commit operation finalizes the transaction, ensuring that all changes made during the transaction are saved permanently to the database.
• The Rollback process cancels a transaction if an error occurs or if it is explicitly invoked, reverting the database to the state it was in before the transaction started.
• Mechanisms for Concurrency Control are implemented to allow multiple transactions to execute simultaneously without conflict, maintaining data consistency and isolation.
• Isolation Levels define the extent to which a transaction is isolated from others, with levels like Read Uncommitted, Read Committed, Repeatable Read, and Serializable offering varying degrees of isolation.
• The Atomicity property ensures that a transaction is executed entirely or not at all, maintaining data integrity in case of a failure.
• Consistency guarantees that a database transitions from one valid state to another, adhering to defined rules and constraints throughout the transaction process.
• The Durability principle ensures that once a transaction is committed, its changes are permanently stored, even in the event of a system failure.
• Locking Mechanisms such as shared locks and exclusive locks are employed to control access to resources and prevent conflicts between concurrent transactions.
• Deadlocks may occur when transactions wait indefinitely for resources locked by each other, requiring the database to detect and resolve such situations.
• Optimistic Concurrency Control assumes minimal conflict and checks for data integrity at the commit stage, avoiding locks during transaction execution.
• Pessimistic Concurrency Control uses locks to prevent conflicts upfront, ensuring that no other transaction modifies the data until the current one is complete.
• A Savepoint is a point within a transaction that allows partial rollbacks, enabling recovery to a specific state without undoing the entire transaction.
• Two-Phase Commit (2PC) is a protocol used in distributed transactions to ensure all participating nodes agree on the commit, enhancing reliability.
• Transaction Logs maintain a record of all changes made during a transaction, supporting recovery processes in case of failures.
• Read Phenomena like dirty reads, non-repeatable reads, and phantom reads are controlled by varying isolation levels to balance performance and consistency.
• Database Management Systems (DBMS) enforce ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliable transaction processing.