Course: MySQL Training
Topic: Understanding ACID Properties
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
ACID properties are fundamental principles of database transaction management. They help maintain data accuracy, reliability, and integrity when database operations are performed, especially when multiple operations must succeed or fail together.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties help databases manage transactions safely, even when errors occur, several users access data simultaneously, or a system unexpectedly stops working.
In MySQL, the InnoDB storage engine supports ACID transactions through features such as COMMIT, ROLLBACK, transaction isolation, locking, and crash recovery.
A transaction is a logical unit of work containing one or more database operations. These operations are treated as a single unit that can be committed or rolled back.
For example, transferring ₹1,000 from one bank account to another involves two operations:
Both operations should succeed together. If the deduction succeeds but the deposit fails, the database must not leave the transfer partially completed. A transaction helps manage this requirement.
After completing this topic, learners should be able to:
| Property | Full Form | Main Purpose | Example |
|---|---|---|---|
| A | Atomicity | Ensures a transaction is completed as a whole or rolled back. | A money transfer must not deduct money without crediting the receiver. |
| C | Consistency | Helps preserve database rules and valid states. | An account balance must follow the application's defined constraints. |
| I | Isolation | Controls how concurrent transactions interact. | Two users updating the same account should not produce conflicting results. |
| D | Durability | Ensures committed changes survive failures according to the database's durability guarantees. | A committed payment should remain recorded after a server restart. |
Atomicity means that all operations within a transaction are treated as one unit. Either the transaction completes successfully, or its changes are rolled back so that a partial result is not left behind.
Atomicity is often summarized as "all or nothing."
Consider a bank transfer in which ₹1,000 is transferred from Account A to Account B.
Suppose the initial balances are:
After a successful transfer, the expected balances are:
If the second operation fails, the first operation should not remain permanently applied as part of a failed transaction. ROLLBACK can undo the uncommitted changes made by the transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;If an error occurs before the transaction commits, the application can roll back the transaction.
ROLLBACK;Important: ROLLBACK must execute while the transaction is still active. Once COMMIT succeeds, a normal ROLLBACK cannot undo that committed transaction. A robust application should check that both accounts exist, that the sender has sufficient funds, and that the required updates actually affect the intended rows before committing.
Consistency means that a transaction should preserve the database's defined integrity rules. A successful transaction takes the database from one valid state to another valid state.
These rules may include primary keys, foreign keys, unique constraints, CHECK constraints, data types, and application-level business rules.
Suppose a product inventory system requires stock quantity to remain nonnegative. A transaction that sells five units should not leave the stock quantity at a negative value.
For example, if the available stock is 12 units and a customer purchases 5 units, the new stock should be 7 units. If the application attempts to sell more units than are available, the business rule should prevent the invalid operation.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
stock_quantity INT NOT NULL,
CONSTRAINT chk_stock_nonnegative
CHECK (stock_quantity >= 0)
) ENGINE = InnoDB;The CHECK constraint expresses a database rule that stock quantity must not be negative.
An inventory deduction can be written as:
START TRANSACTION;
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101
AND stock_quantity >= 5;
COMMIT;In a real application, check the affected-row count. If no row was updated, the product may not exist or may have insufficient stock. The application should not treat that outcome as a successful sale; it should roll back or otherwise handle the failed business operation before completing the transaction.
Isolation controls how concurrently executing transactions interact with each other. It helps prevent one transaction from observing or interfering with another transaction's incomplete work in ways prohibited by the selected isolation level.
Isolation does not necessarily mean that every transaction runs completely separately. Instead, the database applies an isolation level that defines the visibility and concurrency rules for transactions.
Imagine two users trying to purchase the last available item in an online store at almost the same time. If both operations independently assume the item is available, the system could oversell the product without suitable concurrency controls.
Transactions, conditional updates, and appropriate locking can coordinate the operations so that the available stock is handled correctly.
A dirty read occurs when one transaction reads a change made by another transaction before that change has been committed. If the other transaction rolls back, the first transaction has read data that was never committed.
A non-repeatable read occurs when a transaction reads the same row twice and obtains different values because another transaction committed an update between the reads.
A phantom read occurs when a transaction repeats a query that searches for rows matching a condition and sees a different set of rows because another transaction inserted or deleted matching records.
| Problem | Description | Example |
|---|---|---|
| Dirty Read | Reads uncommitted changes. | One transaction reads a balance that another transaction later rolls back. |
| Non-Repeatable Read | The same row returns a different committed value during a transaction. | A balance changes between two reads. |
| Phantom Read | A repeated condition-based query returns a different set of rows. | A second query finds an additional order that matches the search condition. |
InnoDB supports multiple transaction isolation levels. The selected level influences consistent reads, locking, and which changes concurrent transactions can observe.
In MySQL 8.4, REPEATABLE READ is the default isolation level for InnoDB. The exact behavior depends on the isolation level and whether an operation is a consistent read or a locking read.
READ UNCOMMITTED is the least restrictive isolation level. It can permit dirty reads, meaning transactions may see data changed by another transaction before that change is committed.
Example use: Specialized scenarios where approximate information is acceptable and weaker read consistency is an intentional trade-off.
READ COMMITTED allows a transaction to read committed data. Each consistent read obtains a fresh snapshot, so repeated queries can observe changes committed by other transactions between those reads.
Example use: Applications that prefer fresh committed data on each read and accept the associated concurrency behavior.
REPEATABLE READ is the default isolation level for InnoDB in MySQL 8.4. Consistent reads within the same transaction generally use the snapshot established by the first consistent read, helping repeated reads remain consistent.
Locking reads and data-modification statements have additional locking behavior, so not every statement necessarily observes data in exactly the same way as a plain SELECT.
Example use: Many general-purpose transactional applications requiring consistent repeated reads.
SERIALIZABLE is the strictest of these standard isolation levels. It provides stronger restrictions on concurrent transactions and can reduce concurrency because operations may need to wait for other transactions.
Example use: Specialized operations where stricter transaction ordering is important and the performance impact is acceptable.
| Isolation Level | Dirty Reads | Non-Repeatable Reads | General Behavior |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Allows the weakest read consistency. |
| READ COMMITTED | Prevented | Possible | Each consistent read uses a fresh committed snapshot. |
| REPEATABLE READ | Prevented | Prevented for consistent reads within the transaction snapshot | Provides repeatable consistent reads; locking operations have additional rules. |
| SERIALIZABLE | Prevented | Prevented | Applies stricter concurrency controls. |
Note: Phantom-read behavior depends on the isolation level and the type of read. InnoDB's REPEATABLE READ uses locking mechanisms such as next-key locks in relevant locking operations to help prevent certain phantom rows. Do not assume that every database engine implements these levels identically.
You can set an isolation level for the next transaction in a session using the following syntax:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT *
FROM products;
COMMIT;The SET TRANSACTION statement without SESSION or GLOBAL scope applies to the next transaction, subject to MySQL's transaction rules. Set the isolation level before starting the transaction.
Durability means that once a transaction has been successfully committed, its changes are intended to persist even if the database server subsequently experiences a crash or restart.
Durability is particularly important in financial applications, order processing, payroll systems, and other applications where losing committed changes could cause serious problems.
Suppose a customer completes an online payment and the database confirms the transaction. If the database server restarts unexpectedly, the committed payment record should remain available after recovery, subject to the system's configured durability guarantees.
START TRANSACTION;
INSERT INTO orders
(order_id, customer_id, order_total)
VALUES
(1001, 501, 2500.00);
COMMIT;After COMMIT succeeds, the order is committed. InnoDB's logging and recovery mechanisms help preserve committed changes across a server crash. However, the exact durability guarantee also depends on configuration, operating-system behavior, and storage hardware.
A transaction groups SQL operations so they can be committed or rolled back together. In MySQL, common transaction statements include START TRANSACTION, COMMIT, and ROLLBACK.
START TRANSACTION begins a transaction explicitly.
START TRANSACTION;COMMIT successfully completes the transaction and makes its changes permanent.
COMMIT;ROLLBACK cancels the changes made by the current uncommitted transaction.
ROLLBACK;MySQL normally operates with autocommit enabled. In this mode, each successful statement outside an explicitly managed transaction is committed automatically. Use an explicit transaction when multiple statements must be handled as a single unit.
This example demonstrates how a transaction can transfer money between two accounts.
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_holder VARCHAR(100) NOT NULL,
balance DECIMAL(12,2) NOT NULL,
CONSTRAINT chk_balance_nonnegative
CHECK (balance >= 0)
) ENGINE = InnoDB;
INSERT INTO accounts
(account_id, account_holder, balance)
VALUES
(1, 'Aarav', 5000.00),
(2, 'Meera', 3000.00);The following SQL illustrates the transaction structure. In a production application, validate the input, check affected-row counts, and handle errors before committing.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1
AND balance >= 1000;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;For a safe implementation, the application should verify that the sender debit updated exactly one row and that the receiver credit updated exactly one row. If either condition fails, execute ROLLBACK instead of COMMIT. These checks should occur before the transaction ends.
SELECT account_id, account_holder, balance
FROM accounts
ORDER BY account_id;After a successful transfer, the expected balances are:
| Account Holder | Initial Balance | Transfer | Final Balance |
|---|---|---|---|
| Aarav | 5000.00 | -1000.00 | 4000.00 |
| Meera | 3000.00 | +1000.00 | 4000.00 |
ACID properties and database constraints are related but different concepts. ACID describes transaction reliability principles, while constraints enforce particular rules on stored data.
| Concept | Purpose | Example |
|---|---|---|
| Atomicity | Prevents a transaction from being partially committed. | Rollback a failed transfer. |
| Consistency | Preserves defined database rules across transactions. | Prevent invalid foreign-key references. |
| Isolation | Controls interactions between concurrent transactions. | Coordinate simultaneous inventory deductions. |
| Durability | Preserves committed changes. | Recover committed orders after a crash. |
| Primary key | Uniquely identifies each row. | Each account has a unique account ID. |
| Foreign key | Enforces a relationship between tables. | An order references an existing customer. |
| CHECK constraint | Restricts values using a condition. | A stock quantity cannot be negative. |
| Problem | Possible Cause | Recommended Solution |
|---|---|---|
| Changes are not saved | The transaction was not committed or was rolled back. | Check the transaction flow and confirm that COMMIT succeeded. |
| Only part of an operation is completed | The application commits before checking all required operations. | Group related changes in one transaction and validate results before committing. |
| Invalid data is inserted | Required constraints or business validations are missing. | Add suitable constraints and validate application inputs. |
| Transactions wait for one another | Concurrent operations hold conflicting locks. | Keep transactions short and review lock usage and query indexes. |
| Deadlock occurs | Transactions acquire conflicting locks in an incompatible order. | Handle deadlock errors by retrying the entire transaction when safe. |
| Unexpected read results | The isolation level or read type does not match application expectations. | Review isolation settings and the use of consistent or locking reads. |
| Changes disappear after an error | The transaction was rolled back or never committed. | Inspect error handling and the transaction boundary. |
| Committed data is lost after a serious failure | Durability configuration, storage failure, or recovery limitations. | Review InnoDB logging and synchronization settings, hardware reliability, backups, and recovery procedures. |
Project objective: Create an inventory transaction that reduces product stock and records a customer order as one logical operation.
CREATE TABLE inventory_products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
stock_quantity INT NOT NULL,
CONSTRAINT chk_inventory_stock
CHECK (stock_quantity >= 0)
) ENGINE = InnoDB;
CREATE TABLE customer_orders (
order_id INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL,
CONSTRAINT chk_order_quantity
CHECK (quantity > 0),
CONSTRAINT fk_order_product
FOREIGN KEY (product_id)
REFERENCES inventory_products(product_id)
) ENGINE = InnoDB;INSERT INTO inventory_products
(product_id, product_name, stock_quantity)
VALUES
(101, 'Keyboard', 20),
(102, 'Mouse', 15);The following illustrates the SQL operations involved. In application code, check the stock update's affected-row count before inserting the order, and roll back if the update did not succeed. The example order ID must also be unique.
START TRANSACTION;
UPDATE inventory_products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 101
AND stock_quantity >= 2;
-- Verify that exactly one product row was updated.
-- If not, ROLLBACK and stop processing.
INSERT INTO customer_orders
(order_id, product_id, quantity)
VALUES
(5001, 101, 2);
COMMIT;In a complete implementation, the application must branch based on the UPDATE result and execute ROLLBACK on failure. The comments above represent required application logic rather than executable SQL conditions.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe key aspects of reliable database transaction processing.
Atomicity means that a transaction's operations are treated as one unit: the transaction commits as a whole or its uncommitted changes are rolled back.
Consistency means that successful transactions preserve the database's defined integrity rules and business invariants.
Isolation controls how concurrent transactions interact and what changes they can observe.
Durability means that committed changes are intended to persist through failures covered by the database's durability guarantees.
InnoDB supports ACID transactions and is the default storage engine in MySQL 8.4.
COMMIT completes a transaction and makes its changes permanent. ROLLBACK reverses changes made by the current uncommitted transaction.
A dirty read occurs when a transaction reads another transaction's uncommitted changes.
The default isolation level is REPEATABLE READ.
A deadlock occurs when transactions wait on one another's locks in a cycle, preventing them from proceeding until the database detects and resolves the situation.
No. ACID describes transaction reliability properties, while constraints enforce specific data integrity rules.
They help ensure that related operations, such as debiting one account and crediting another, are handled together instead of leaving partial changes.
No. A normal ROLLBACK cannot undo a transaction that has already been committed. A compensating transaction or another corrective operation may be required.
Crash recovery helps restore database consistency after certain failures, but backups are needed for recovery from accidental deletion, corruption, hardware loss, and other disasters.
Isolation controls visibility and concurrency so that simultaneous transactions follow the rules of the selected isolation level.
ACID properties are essential concepts for understanding reliable database transactions in MySQL. Atomicity prevents partial transaction completion, Consistency helps preserve valid data, Isolation governs concurrent operations, and Durability protects committed changes against failures covered by the system's guarantees.
By combining InnoDB transactions with appropriate constraints, careful error handling, suitable isolation levels, and tested backup and recovery procedures, developers can build safer and more dependable database applications.
Course Link: Explore MySQL Training at JustAcademy
Demo Link: Register for a Course Demo
Official MySQL References:
