Database Transactions Explained

Let’s say Alex wants to send 100 rupees to a friend. Sounds simple, right? But inside the database, that one click is actually two steps:

• First, take 100 out of Alex’s account.
• Then, add 100 to the friend’s account.

Now here’s the scary part. What if the first step works, but the second one fails halfway? The money has left Alex’s account, but it never reached the friend. It just vanished. That’s the kind of bug that gets a bank in serious trouble.

So we need a way to say: either both steps happen, or neither does. That’s exactly what a transaction is for. Let’s see how it works.

🎯 The Problem

The trouble starts the moment one logical action needs more than one change in the database. Think about it:

• A money transfer is two writes: subtract from one account, add to another.
• Placing an order is many writes: save the order, reduce the stock, charge the card.
• These steps run one after another, and any of them can fail. The server can crash, the network can drop, the disk can fill up.

If a step fails in the middle, you’re left with a partial update. A partial update means some changes went through and some didn’t, so the data no longer makes sense. Money is gone but not received. An order exists but the stock was never reduced. Your data is now broken, and broken data is really hard to clean up later.

What we want is simple to say. We want the whole group of steps to behave like one single action that either fully happens or fully doesn’t. No half-finished mess in between.

💳 What is a Transaction

A transaction is a group of database operations that the database treats as one single all-or-nothing unit. That’s the whole idea in one line. Let’s unpack it:

• “Group of operations” means several reads and writes bundled together, like our subtract-then-add money transfer.
• “All-or-nothing” means the database promises that either every operation in the group succeeds, or none of them take effect at all.
• There is no middle state where some steps stuck and others didn’t. From the outside, it looks like one clean action.

So going back to Alex’s transfer, we wrap both steps inside one transaction. Now the database guarantees that the money either fully moves or doesn’t move at all. The “vanished money” situation simply can’t happen anymore.

✅ Commit and Rollback

So how does the database actually deliver that all-or-nothing promise? With two simple actions, commit and rollback.

• A commit means make all the changes in the transaction permanent. Every step worked, so the database saves them for good and tells everyone “this is now official.”
• A rollback means undo everything in the transaction. Something went wrong in the middle, so the database throws away all the changes and puts the data back exactly the way it was before the transaction started.

The flow is easy to picture. The database starts the transaction, runs the steps one by one, and then asks a single question at the end: did everything work? If yes, it commits. If no, it rolls back.

Here’s Alex’s transfer written out as a transaction. Notice how the two writes sit between a begin and an end:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alex';
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Friend';

COMMIT;

Let’s read it line by line:

• BEGIN tells the database “a transaction is starting, treat what follows as one unit.”
• The two UPDATE lines are our steps: take 100 from Alex, give 100 to the friend.
• COMMIT says “both worked, make it permanent.”

And if something breaks in the middle, the application tells the database to undo it instead:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alex';
-- the next step fails here, so we undo everything

ROLLBACK;

That ROLLBACK wipes out the first UPDATE too. Alex’s balance goes right back to what it was. No money lost.

🔒 Transactions and ACID

You may have heard of ACID, the set of guarantees a good database gives you. Transactions are how the database actually delivers those guarantees. Here’s the quick link back:

• Atomicity is the all-or-nothing promise we just covered. Commit and rollback are literally how the database keeps it.
• Consistency means the data follows its rules before and after. A transaction takes the data from one valid state to another valid state, never leaving it half-broken.
• Isolation means transactions running at the same time don’t step on each other. We’ll look at this next.
• Durability means once you commit, the changes stay saved even if the server crashes a second later.

So when people say “this database supports ACID,” what they really mean is “you can safely wrap your operations in transactions and trust them.” The transaction is the tool. ACID is the set of promises that tool keeps.

🔀 Isolation Levels

In the real world, lots of transactions run at the same time. Many people transferring money, ordering, checking balances, all at once. Isolation levels are the settings that control how much one running transaction can see of another one that hasn’t finished yet.

Before naming them, one term to know. A dirty read is when a transaction reads data that another transaction has changed but not committed yet. The problem is that the other transaction might still roll back, so you just read a value that never really existed.

Here are the common isolation levels, from loose to strict:

• Read committed means a transaction can only read data that has actually been committed. This blocks dirty reads, and it’s the everyday default in many databases.
• Repeatable read means if you read the same row twice in one transaction, you get the same value both times, even if someone else changed it in between.
• Serializable is the strictest. It makes transactions behave as if they ran one after another in a line, with no overlap at all. Safest, but slowest.

The trade-off is the thing to remember. Stricter isolation means fewer surprises but slower performance, because the database has to do more locking and waiting. Looser isolation is faster but can let some odd reads slip through.

✅ When You Need Transactions

You don’t wrap everything in a transaction. You reach for one whenever a single logical action needs several changes that must all stick together. Common cases:

• Money. Transfers, payments, refunds. The classic all-or-nothing case.
• Orders. Save the order, reduce inventory, charge the customer. If any part fails, none should count.
• Inventory. Moving stock between warehouses, where one side goes down and the other goes up together.
• Any multi-step change where a partial update would leave the data in a state that doesn’t make sense.

The simple test is this. Ask yourself: “If only half of these steps went through, would my data be wrong?” If the answer is yes, you want a transaction.

⚠️ Common Mistakes and Misconceptions

A few things trip people up early. Let’s clear them out:

• “Every operation needs a transaction.” No. A single read or a single independent write is fine on its own. Transactions are for groups of steps that must succeed or fail together. Wrapping everything just adds overhead.
• “I started a transaction, so failures handle themselves.” Not quite. You still have to catch the error and actually call rollback. Forgetting to handle the failure path can leave a transaction hanging open, holding locks and blocking others.
• “Bigger transactions are safer.” The opposite, often. A long transaction holds locks for a long time and makes everyone else wait, which hurts performance and can cause deadlocks. Keep transactions short and focused.
• “Commit and rollback are slow extras.” They’re the whole point. They’re what give you the all-or-nothing safety. Skipping them is what gets you into the broken-data mess.

🛠️ Design Challenge

Try this on your own to test yourself.

Imagine a ticket-booking system for a concert. A user clicks “book,” and the system has to: check that a seat is free, mark that seat as taken, and charge the user’s card. Now think through these:

• Which of these steps belong inside one transaction, and why?
• What goes wrong if the charge fails after the seat is already marked taken, and there’s no rollback?
• Two users click “book” for the last remaining seat at the exact same time. Which isolation level would help stop them from both getting it?

Write down your reasoning. This is exactly the kind of thinking interviewers love to see.

🧩 What You’ve Learned

You can now explain what transactions are and why they matter. Here’s what you’ve picked up:

• ✅ A transaction is a group of operations treated as one all-or-nothing unit.
• ✅ Commit makes all the changes permanent; rollback undoes everything if any step fails.
• ✅ Transactions are how databases deliver ACID, especially atomicity and durability.
• ✅ Isolation levels control what concurrent transactions can see of each other, trading speed for safety.
• ✅ You reach for a transaction whenever a partial update would leave your data wrong.

🚀 What’s Next?

You’ve got the core idea down. Next, go deeper into the guarantees and how databases stay reliable at scale.

• ACID Properties breaks down each of the four guarantees that transactions deliver.
• Database Replication shows how data stays safe and available across many servers.