Beyond CRUD: Building Reliable Software Systems · Part 3
Database Transactions Explained: Keeping Data Correct When Things Go Wrong
“Transactions are not just a database feature—they’re one of the fundamental building blocks of reliable software.”
Imagine you’re transferring KES 10,000 from your savings account to a friend.
From your perspective, it’s a single action. You tap “Send Money”, authenticate the transaction, and wait for confirmation. Behind the scenes, however, the banking system performs several independent operations. It verifies that you have sufficient funds, deducts the amount from your account, credits your friend’s account, records the transaction in a ledger, updates account balances, and generates a receipt.
Each of these operations is important, but together they represent a single business action: a money transfer.
Now imagine the server crashes immediately after deducting the money from your account but before crediting your friend.
The result is disastrous. Your balance has decreased, your friend never receives the money, and unless additional recovery mechanisms exist, the system is left in an inconsistent state. From a customer’s perspective, the money has simply disappeared.
The same problem appears outside banking.
An e-commerce application might create an order, reduce inventory, process a payment, generate an invoice, and send a confirmation email. If the payment succeeds but the order creation fails, the customer has paid for a product that the system doesn’t believe exists. Likewise, in a loan management system, a repayment may update the outstanding balance, post accounting entries, and generate a receipt. If only some of those updates complete, financial records quickly become unreliable.
These problems aren’t caused by bad algorithms or poor business logic. They’re caused by partial execution when only part of a larger operation succeeds.
This is precisely the problem database transactions were designed to solve.
A transaction ensures that multiple database operations behave as a single unit of work. Either every operation succeeds together, or every operation is rolled back as though nothing ever happened. There is no halfway point where your system is left in an inconsistent state.
For backend developers, understanding transactions is just as important as understanding APIs or databases themselves. They are the foundation upon which reliable financial systems, booking platforms, inventory systems, healthcare applications, and countless other business-critical systems are built.
What Is a Database Transaction?
A database transaction is a collection of one or more database operations that the database treats as a single logical operation.
Instead of thinking about individual SQL statements, think about the business process they represent.
Suppose a customer purchases the last laptop in your online store. That single purchase might require your application to:
- Create an order.
- Deduct one item from inventory.
- Reserve the shipment.
- Record the payment.
- Create an invoice.
Although these are separate SQL statements, they represent one business event. Either all of them should succeed, or none of them should.
That’s exactly what a transaction guarantees.
Without transactions, every statement executes independently. If statement number four fails, the previous three remain committed, leaving your data inconsistent.
With transactions, the database waits until you’re satisfied that every operation has completed successfully. Only then are the changes permanently saved.
Understanding Transactions Through a Simple Example
Let’s return to the banking example.
Alice wants to transfer KES 10,000 to Bob.
A simplified version of the SQL might look like this:
1
2
3
4
5
6
7
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 10000
WHERE account_id = 2;
At first glance, this seems perfectly reasonable.
But imagine the database crashes immediately after the first statement executes.
Alice’s balance has already been reduced.
Bob’s balance has not increased.
The system now contains incorrect financial data.
This is why production systems rarely execute related operations independently.
Instead, they wrap them inside a transaction.
1
2
3
4
5
6
7
8
9
10
11
BEGIN;
UPDATE accounts
SET balance = balance - 10000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 10000
WHERE account_id = 2;
COMMIT;
If every statement succeeds, the database executes the COMMIT, making the changes permanent.
If any statement fails before that point, the application issues a ROLLBACK, and the database restores itself to exactly the state it was in before the transaction began.
To the outside world, it appears as though the failed transfer never happened.
This “all-or-nothing” behavior is what makes transactions so valuable.
The Lifecycle of a Transaction
Every transaction follows a predictable lifecycle.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
│
Execute SQL Operations
│
Everything Successful?
│
┌─ Yes ─────────────┐
│ │
COMMIT Changes Saved
│
└─ No ──────────────┐
│
ROLLBACK
│
Database Restored
The process begins with BEGIN, which tells the database to temporarily hold all modifications rather than immediately committing them.
The application then performs one or more operations. These could involve inserting records, updating balances, deleting data, or modifying relationships between tables.
If every operation succeeds, the application calls COMMIT. At that point, the database permanently saves all changes.
If anything goes wrong along the way—perhaps a validation error, a database constraint violation, or even an unexpected server failure—the transaction is rolled back, discarding every change made since BEGIN.
The beauty of this model is that the database itself guarantees consistency. Developers don’t need to manually undo every failed operation because the database handles that responsibility.
The Four ACID Properties
When developers discuss transactions, you’ll almost always hear the term ACID.
Despite sounding intimidating, ACID simply describes the guarantees that modern relational databases provide when executing transactions.
Atomicity
Atomicity means that a transaction is indivisible.
Either every operation succeeds, or none of them do.
Returning to our banking example, it makes no sense for money to be deducted from one account without being added to another. The transaction must succeed completely or fail completely.
Think of flipping a light switch.
The light cannot be half on.
Similarly, a transaction cannot be half completed.
Consistency
Consistency ensures that every transaction leaves the database in a valid state.
Business rules should always remain true.
If your application enforces that inventory can never become negative, then a successful transaction should never violate that rule.
Likewise, if your accounting system requires every journal entry to balance, no committed transaction should ever leave the ledger unbalanced.
Consistency isn’t about preventing bugs in your application logic; it’s about ensuring that completed transactions respect the rules defined by your database and your business.
Isolation
Isolation becomes important when multiple users interact with the system simultaneously.
Imagine two customers attempting to purchase the last available ticket for a concert.
Without proper isolation, both requests may read the inventory before either updates it. Both believe the ticket is available, and both complete the purchase.
You’ve now sold the same seat twice.
Isolation ensures that concurrent transactions don’t interfere with one another in ways that produce inconsistent results.
In our previous article, we discussed race conditions situations where multiple requests compete to modify the same data. Isolation is one of the database mechanisms used to prevent those concurrency problems.
We’ll explore isolation levels in greater depth in the next article because they deserve an entire discussion of their own.
Durability
Durability guarantees that once a transaction has been committed, the changes are permanent.
Even if the server loses power immediately after the commit, the database ensures that committed data survives.
Modern databases achieve this through techniques such as write-ahead logging, transaction logs, and crash recovery.
For developers, the important takeaway is simple: once the database confirms a successful commit, you can trust that the data has been safely stored.
Transactions Solve Partial Failures: Not Every Problem
One misconception among newer developers is that transactions magically solve every data consistency problem.
They don’t.
Transactions protect against partial execution.
Suppose your application updates three tables and crashes after updating the second one.
A transaction ensures that the database rolls everything back, preventing inconsistent data.
However, transactions don’t automatically solve concurrency problems.
Imagine two users attempting to withdraw money from the same account simultaneously.
Each transaction independently checks the balance before either completes.
If both see the same balance and both proceed, the final result may still be incorrect depending on your isolation level.
This isn’t a transaction problem.
It’s a concurrency problem.
That’s why understanding race conditions and transactions together is so important. They solve different classes of reliability issues.
Common Places You’ll Use Transactions
Transactions appear almost everywhere in modern backend systems.
Payment processing is perhaps the most obvious example. Charging a customer’s card, recording the payment, updating invoices, and generating accounting entries should either all succeed or all fail together.
Inventory management systems use transactions to ensure stock counts remain accurate even when multiple customers are purchasing products simultaneously.
Booking platforms rely on transactions to reserve hotel rooms, airline seats, or event tickets without creating conflicting reservations.
Loan management systems use transactions when posting repayments, updating outstanding balances, calculating accrued interest, and recording accounting entries.
Healthcare systems use them to ensure patient records, prescriptions, billing information, and appointment schedules remain synchronized.
Any time a business operation spans multiple database changes, a transaction is usually involved.
Common Mistakes Developers Make
One of the most common mistakes is keeping transactions open for too long.
Imagine starting a transaction, calling a third-party payment API, waiting several seconds for a response, and only then committing the transaction.
During that entire period, database resources may remain locked, reducing performance for other users.
A better approach is to perform external API calls before starting the transaction whenever possible, keeping the transaction focused solely on database operations.
Another common mistake is assuming transactions automatically protect against concurrent updates. As we’ve already seen, concurrency introduces an entirely different set of challenges that require locking strategies or appropriate isolation levels.
Finally, developers sometimes forget that transactions should represent business operations not individual SQL statements. Wrapping every single query in its own transaction rarely provides meaningful benefits.
Transactions in Modern Frameworks
Fortunately, most frameworks make transactions straightforward to use.
Laravel offers the DB::transaction() helper.
Django provides transaction.atomic().
Entity Framework supports BeginTransactionAsync().
Spring Boot uses the @Transactional annotation.
Although the syntax differs, the underlying principle never changes. The framework simply tells the database when to begin the transaction, when to commit it, and when to roll it back if something goes wrong.
Understanding the concept matters far more than memorizing framework-specific syntax.
Transactions, Idempotency, and Race Conditions
If you’ve been following this series, you may have noticed that each concept addresses a different reliability challenge.
Idempotency protects against duplicate requests by ensuring that repeating the same request doesn’t produce duplicate side effects.
Race conditions occur when multiple requests compete to modify shared data simultaneously, leading to unpredictable outcomes.
Transactions ensure that a group of related database operations either all succeed together or all fail together.
Reliable backend systems typically rely on all three.
Imagine a payment API.
Idempotency prevents customers from being charged twice if they retry a request.
Transactions ensure that charging the customer, recording the payment, and updating account balances either all succeed or all fail.
Proper concurrency control ensures that two simultaneous payment requests don’t corrupt shared data.
Each concept complements the others rather than replacing them.
Final Thoughts
Transactions are one of the reasons relational databases remain so powerful. They provide developers with a reliable mechanism for preserving data integrity even when failures occur.
As systems become larger and more distributed, failures become inevitable. Servers crash, networks fail, APIs time out, and users submit requests simultaneously. Transactions don’t eliminate those realities, but they ensure your database remains consistent when they happen.
Whenever you’re implementing a feature that modifies multiple pieces of related data, pause for a moment and ask yourself:
What happens if this operation fails halfway through?
If the answer is “my system ends up in an inconsistent state,” then you’ve almost certainly found a place where a database transaction belongs.
In the next article, we’ll build on this foundation by exploring database isolation levels and why two perfectly valid transactions can still interfere with one another when they run at the same time.