ACID Transactions
What a Database Transaction Is
ACID transactions are database transactions that follow four guarantees: atomicity, consistency, isolation and durability. A transaction is a group of operations that the database treats as a single unit of work.
Take a bank transfer. The database must subtract an amount from one account and add it to another. If the system crashes after the first step and before the second, money appears to vanish. A transaction prevents this by treating both steps as one unit. The changes become permanent only when the transaction is committed. If an error occurs first, the database rolls back and undoes the steps already made.
The Four ACID Properties Explained
Each letter in ACID stands for one property that the database promises to keep.
- Atomicity means all of the operations succeed or none of them do. A half-finished transaction is never left behind.
- Consistency means a transaction moves the database from one valid state to another. Rules such as unique keys, foreign keys and value checks must still hold when it finishes.
- Isolation means concurrent transactions do not see each other's unfinished work. Databases offer several isolation levels, which trade strictness against how much transactions can interfere with each other.
- Durability means that once a commit is confirmed, the change survives a crash or power failure. Databases usually achieve this by writing changes to a log on disk before they confirm the commit.
Why Orders and Payments Depend on Transactions
An online checkout touches several tables at once. It creates an order record, reduces the stock count and stores a payment record. Each of those writes is a separate change, and any of them could fail.
Without transactions, a failed payment after the stock was reduced would leave the inventory wrong. With a transaction, the order, the stock change and the payment are committed together or not at all. The same reasoning applies to account balances, invoices and bookings.
Not every operation needs the full guarantees. Tables that use a transaction-capable storage engine, such as InnoDB in MySQL, offer these guarantees. Systems spread across several servers face extra trade-offs, which are described in the CAP theorem.
Frequently asked questions
Do all databases support ACID transactions?
No. Most relational databases, including PostgreSQL and MySQL with the InnoDB engine, support ACID transactions. Some storage engines, such as MyISAM in MySQL, do not, and many NoSQL databases offer limited or optional transaction support. Check the documentation for the engine and version you use.
What happens when a transaction fails halfway through?
The database rolls back the transaction, undoing every change it made so far, so the data returns to the state it had before the transaction began. The application can then report the error or retry the operation. Because of this, a failed transaction does not leave partial orders or transfers behind.