Transaction and ACID properties
System Design(SD)(22CS024)
Bachelor of Engineering- Computer Science & Engineering
Department of Computer Science & Engineering
Chitkara University Institute of Engineering and Technology,
Chitkara University, Punjab, India
1
Transaction
A transaction is a single, logical unit of work, comprised of one or more operations that are
treated as an indivisible whole. It’s a sequence of database operations that either succeed or fail
together.
Example: Money Transfer Consider a bank transfer from Account A to Account B. This
seemingly simple action involves multiple steps:
1. Read the balance of Account A.
2. Subtract the transfer amount from Account A's balance.
3. Write the new balance back to Account A.
4. Read the balance of Account B.
5. Add the transfer amount to Account B's balance.
6. Write the new balance back to Account B.
2
If the system fails after step 3 but before step 6, Account A would be debited, but Account
B would not be credited. The money would be lost, and the database would be in an
inconsistent state. A transaction ensures this entire sequence is treated as a single
operation, guaranteeing that either all steps are completed successfully, or none are.
3
States
A transaction in a database can be in one of the following states:
4
Need for Transaction
Transactions are essential for maintaining data integrity in the face of:
● System Failures: Unexpected crashes, power outages, or hardware malfunctions.
● Application Errors: Bugs or exceptions in the application logic.
● Concurrency Issues: Multiple users or processes accessing and modifying the same data
simultaneously.
Without transactions, these events can lead to data loss, corruption, and an inconsistent system
state.
5
Without a transactional model, a system's state is vulnerable to partial updates and
inconsistencies, especially in a world with complex interactions and potential for failure.
● The Problem of Inconsistent State: The "money transfer" example perfectly illustrates
this. If the system crashes after the debit but before the credit, the database is in an
inconsistent state. The total amount of money in the system is now less than it should be,
a critical error in any financial application. This is known as a lost update problem.
6
● Scenario Breakdown:
○ The database holds a total of $200 ($100 in Account A, $100 in Account B).
○ Step 1: The transaction begins.
○ Step 2: Account A is debited. The new state is A=$0, B=$100.
○ Step 3: A system crash occurs. The transaction is not yet complete.
○ Step 4: The system restarts. The last known state in the database is A=$0, B=$100.
The $100 from Account B is lost.
● The Solution: Transactions solve this by introducing the concepts of Commit and
Rollback. The changes are not made permanent until the transaction is committed. If a
failure occurs before a commit, the system simply rolls back all uncommitted changes,
preserving data integrity.
7
Use of Transactions
Transactions are crucial for applications that require high data reliability, such as:
● Financial systems: Processing money transfers between accounts.
● E-commerce: Placing orders, which involves updating inventory, processing payments, and
managing customer records.
● Booking systems: Reserving flights or hotel rooms.
8
Introducing the ACID Properties
ACID is an acronym for four key properties that guarantee the reliability of database
transactions. These properties were originally defined in the late 1970s and have since
become the standard for traditional relational database management systems (RDBMS).
A - Atomicity
● Concept: "All or Nothing."
● Principle: A transaction is an indivisible unit of work. It either completes all of its
operations successfully and commits, or it fails, and all of its operations are rolled back
(undone).
● Implementation: Achieved through a transaction log (or journal). Before a change is
applied, the database logs the intended operation. If the transaction fails, the log is used
to undo the partial changes, restoring the database to its pre-transaction state.
A
9
● Technical Implementation: This is primarily achieved through transaction logs and
rollback segments. Before an operation (like an UPDATE or DELETE) is executed, the
database writes the "before" state of the data to a transaction log. If the transaction fails, the
database can use this log to restore the data to its original state. This is known as rollback.
Two key points :
● Commit: If the transaction is successful, the changes are permanently applied.
● Abort/Rollback: If the transaction fails, any changes made during the transaction are
discarded.
10
Example: Consider the following transaction T consisting of T1 and T2 : Transfer of
$100 from account X to account Y .
If the transaction fails after completion of T1 but before completion of T2, the
database would be left in an inconsistent state. With Atomicity, if any part of the
transaction fails, the entire process is rolled back to its original state, and no
partial changes are made 11
C - Consistency
Consistency ensures that a transaction, when committed, leaves the database in a valid state.
Consistency
● Concept: "Valid State Transitions."
● Principle: A transaction must bring the database from one valid state to another. It must adhere to all
predefined rules, constraints, and triggers (e.g., unique keys, foreign keys, check constraints).
● Example: If a rule states that an account balance cannot be negative, a transaction that attempts to debit an
account to a negative value will be aborted to maintain consistency. Consistency is often the responsibility
of both the database (via constraints) and the application (via business logic).
● In a banking system, the rule "an account balance must be non-negative" is a crucial consistency constraint.
If a transaction attempts to withdraw more money than is available, the transaction will be aborted by the
system because it would violate the consistency rule. This ensures the database's integrity is never
compromised.
12
Example: Suppose the sum of all balances in a bank system should always be constant. Before a
transfer, the total balance is $700. After the transaction, the total balance should remain $700. If the
transaction fails in the middle (like updating one account but not the other), the system should maintain
its consistency by rolling back the transaction.
Total before T occurs = 500 + 200 = 700 .
Total after T occurs = 400 + 300 = 700 .
13
I-Isolation
Isolation deals with the problem of concurrency. It ensures that multiple transactions running at the
same time do not interfere with each other.
● Principle: Each transaction should feel as though it is the only one running on the system. If
Transaction A and Transaction B are both trying to update the same record, isolation ensures that
the end result is the same as if one had run completely after the other. This prevents a variety of
concurrency problems.
● Analogy: Think of multiple writers editing the same Google Doc. Without a good isolation
strategy, Writer A might try to edit a sentence at the same time as Writer B, leading to a garbled
mess. Isolation ensures that each writer's changes are applied in a clear, sequential manner.
14
● Isolation Levels: The trade-off between strict isolation and performance is managed
through different isolation levels:
○ Serializable: The highest level of isolation. Transactions are completely isolated and
executed as if they were done one after the other. This prevents all concurrency issues
but can be slow.
○ Read Committed: The most common level. It prevents "dirty reads" (reading data that
has not yet been committed) but can still be susceptible to other anomalies.
It ensures that the result of concurrent transactions is the same as if they were run one after another,
preventing issues like:
● Dirty reads: reading uncommitted data
● Non-repeatable reads: data changes between two reads
● Phantom reads: new rows appear during a transaction
15
Example: Consider two transactions T and T''.
● X = 500, Y = 500
16
D - Durability
Durability ensures that once a transaction is committed, its changes are permanent.
● Concept: This is the property that guarantees persistence. It means that the committed data will
survive any system failure, whether it's a power failure, a software crash, or a hardware
malfunction.
● Technical Implementation: This is achieved by writing the transaction's changes to non-volatile
storage (like a hard disk or SSD) before a commit is acknowledged to the user. A common
method is the Write-Ahead Log (WAL).
○ The transaction's intended changes are first written to the WAL.
○ The commit is acknowledged to the user.
○ The actual data files are updated later.
○ If a crash occurs before the data files are updated, the database can use the WAL to redo the
committed changes upon restart, ensuring no data is lost.
17
Impact of ACID properties to Database
The ACID properties, in totality, provide a mechanism to ensure the correctness and consistency
of a database in a way such that each transaction is a group of operations that acts as a single
unit, produces consistent results, acts in isolation from other operations, and updates that it
makes are durably stored.
1. Data Integrity and Consistency
ACID properties safeguard the data integrity of a DBMS by ensuring that transactions either
complete successfully or leave no trace if interrupted. They prevent partial updates from
corrupting the data and ensure that the database transitions only between valid states.
18
2. Concurrency Control
ACID properties provide a solid framework for managing concurrent transactions. Isolation
ensures that transactions do not interfere with each other, preventing data anomalies such as
lost updates, temporary inconsistency, and uncommitted data.
3. Recovery and Fault Tolerance
Durability ensures that even if a system crashes, the database can recover to a consistent
state. Thanks to the Atomicity and Durability properties, if a transaction fails midway, the
database remains in a consistent state.
19
20
ACID vs. BASE
While ACID is the gold standard for traditional RDBMS, modern distributed systems often adopt
the BASE model, which prioritizes availability and performance over strict consistency.
ACID: The traditional model focused on providing strong data integrity guarantees. It is the
perfect choice for systems where consistency is paramount, such as financial transactions,
inventory management, or any application where a loss of data or integrity would be catastrophic.
The trade-off is often lower scalability and availability in a distributed environment.
BASE: A model adopted by many NoSQL and distributed databases.
21
22
● Basically Available: The system is always available to serve requests, even if some parts of
the system are down or partitioned.
● Soft-state: The state of the system can change over time, even without input, due to
eventual consistency.
● Eventual Consistency: The system will eventually become consistent, but there may be a
delay. For a brief period, different parts of the system might see different, inconsistent data.
● BASE is ideal for systems like social media feeds or user profiles, where it's okay for a
user's recent post to appear a few seconds later for a friend on a different server.
23
24
ACID in Distributed Systems
Maintaining ACID properties across multiple, physically separate machines is a major challenge in
distributed system design, directly related to the CAP Theorem.
● The Problem: Network partitions (a temporary loss of connectivity between nodes) make it impossible
to guarantee both Availability and Consistency. If a partition occurs, a system can either stop accepting
requests (preserving Consistency) or continue accepting requests (sacrificing Consistency).
● Solutions:
○ Two-Phase Commit (2PC): A protocol that aims to achieve Atomicity in a distributed transaction. It
has two phases: a "prepare" phase where all nodes agree to commit, and a "commit" phase where
they all commit. It's a blocking protocol, meaning if one node fails, the whole transaction can be
blocked, leading to poor availability.
○ Saga Pattern: A more modern, non-blocking approach. Instead of a single, all-or-nothing
transaction, a saga is a series of local, independent transactions. If a transaction fails, a
"compensating transaction" is triggered to undo the work done by previous transactions in the saga.
This provides higher availability but trades the guarantee of Atomicity for a less rigid form of
consistency.
25