ACID Transactions
Overview
If you transfer $100 from Alice to Bob, it requires two queries: (1) Deduct $100 from Alice, (2) Add $100 to Bob. If the server loses power exactly between query 1 and query 2, the $100 is permanently deleted from reality. This is unacceptable. A Transaction is a protective wrapper. It groups multiple queries together into a single atomic block. It guarantees that either ALL the queries succeed, or ALL of them completely revert (Rollback) as if nothing ever happened. This is known as ACID (Atomicity, Consistency, Isolation, Durability).
Syntax
-- A standard Financial Transaction
-- 1. Start the protective wrapper
BEGIN TRANSACTION; -- (Or just BEGIN in Postgres)
-- 2. Execute the queries
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- 3. If the backend code confirms everything looks correct...
-- Permanently write the changes to the hard drive!
COMMIT;
-- OR 4. If an error occurs (e.g., Alice has insufficient funds)
-- Instantly undo all queries in the block!
ROLLBACK;Common Pitfalls
- Leaving transactions open. If your Node.js backend runs
BEGIN, updates a row, and then the Node process crashes before sendingCOMMIT, that transaction remains 'Open' on the database. The database will permanently lock that row, preventing any other user from touching it, eventually bringing down the entire system. Always usetry/catch/finallyto ensureROLLBACKis sent on error. - Overloading a transaction. Do not put 3rd-party API calls (like sending an Email or charging Stripe) inside a database transaction block. If Stripe takes 10 seconds to respond, your database row remains heavily locked for 10 seconds. Transactions should only contain hyper-fast database queries.
Interview Questions
'Isolation'. It guarantees that if two transactions are running concurrently, they cannot see each other's half-finished work. For example, if Transaction A deducts $100 but hasn't committed yet, Transaction B querying the balance will still see the original amount.
Real-World Example
How backend Object-Relational Mappers (ORMs) handle transactions natively in code.
// Real-world Node.js/Prisma representation of SQL Transactions
await prisma.$transaction(async (tx) => {
// If ANY of these fail, the entire block instantly Rolls Back!
const order = await tx.order.create({ data: newOrder });
await tx.inventory.decrement({ where: { id: order.item_id } });
await tx.user.update({ balance: -order.cost });
});Check Your Knowledge
Test your understanding of ACID Transactions with these quick questions.