Skip to main content
Maintaining transaction integrity is critical when multiple updates must be applied together in a relational database. In this guide, you’ll learn how to guarantee accurate, reliable, and consistent data using the four ACID properties: Atomicity, Consistency, Isolation, and Durability.

​
Example: Transferring Funds Between Accounts

Consider two bank accounts represented by rows in a database table:
  • Savings: $20
  • Checking: $50
Together, these balances total $70—the amount the bank owes you.
The image shows a step in a money-moving process with a total amount of 70, represented by two bars: one for 20 and another for $50.
To move $10 from your Savings to Checking, you execute a transaction with two updates: 
BEGIN TRANSACTION;

-- 1. Subtract $10 from Savings
UPDATE accounts
SET balance = balance - 10
WHERE account_type = 'Savings';

-- 2. Add $10 to Checking
UPDATE accounts
SET balance = balance + 10
WHERE account_type = 'Checking';

COMMIT;
You can also use ROLLBACK to undo changes if any step fails:
ROLLBACK;

​
Potential Issues Without ACID

  • Incomplete transaction: The first update succeeds, but the second one fails, leaving balances inconsistent.
  • Interim reads: A third party queries balances after step 1 but before step 2, seeing $10 in savings and $50 in checking (total $60).
The image discusses transaction integrity, highlighting potential issues like incomplete steps and data inconsistency, with a visual of a bank and account balance.

​
ACID Properties Explained

The ACID acronym stands for the four rules that guarantee transaction integrity:
PropertyDescription
AtomicityEnsures all updates in a transaction succeed or none do (all-or-nothing).
ConsistencyGuarantees the database moves from one valid state to another (e.g., total balance remains $70).
IsolationPrevents other operations from seeing intermediate states.
DurabilityCommits survive system failures; changes persist even after a crash.
The image explains the ACID transaction criteria, detailing Atomic, Consistent, Isolated, and Durable properties with brief descriptions and examples for each.

​
Isolation Levels

Different databases support multiple isolation levels (e.g., Read Committed, Serializable). Choose the level that balances concurrency and consistency for your workload.
Long-running transactions can block other operations and degrade performance. Keep transactions short and predictable.

​
Transaction Management: Commit vs. Rollback

At the end of your transaction:
  • COMMIT: Applies all updates, resulting in $10 in savings and $60 in checking.
  • ROLLBACK: Undoes every change, preserving the original $20 and $50 balances.
The image illustrates the results of transaction management, showing account balances when all updates succeed versus when all updates fail. It includes a bar chart and text detailing the amounts in savings and checking accounts.

Watch Video