Database Transactions and ACID Properties

It is 9:47 PM. You open your UPI app, select your friend Kabir, type ₹500, and tap "Pay." A spinner appears. Three seconds pass. Then — nothing. No "Success," no "Failed," just a frozen screen because your train enters a tunnel and the network drops.

Now you have a genuine problem, and it is a database problem, not a network problem. Somewhere on a server owned by the National Payments Corporation of India, two numbers needed to change together: your bank balance had to go down by ₹500, and Kabir's balance had to go up by ₹500. If the network died after the first change but before the second, ₹500 has simply vanished from the banking system — deducted from you, credited to no one. If it died before either change, nothing happened and you can safely retry. Your app cannot tell which of these happened just by staring at a frozen spinner.

This exact situation — needing several separate changes to a database to either all happen or none happen, even when crashes, power cuts, or two people acting at once get in the way — is what this chapter is about. The database concept that solves it is called a transaction, and the four guarantees a well-built database gives every transaction are called the ACID properties: Atomicity, Consistency, Isolation, Durability.

What exactly is a "transaction"?

Picture a tiny bank database with one table:

accounts
+---------+-------+---------+
| acc_no  | name  | balance |
+---------+-------+---------+
|   101   | Riya  |  2000   |
|   202   | Kabir |   500   |
+---------+-------+---------+

Riya sends Kabir ₹500 through UPI. Underneath the app's friendly interface, the database has to run two separate SQL statements:

UPDATE accounts SET balance = balance - 500 WHERE acc_no = 101;  -- Riya loses 500
UPDATE accounts SET balance = balance + 500 WHERE acc_no = 202;  -- Kabir gains 500

Each UPDATE on its own is a complete, valid SQL command. But neither one, by itself, represents "a payment." A payment is only meaningful as both together. If line 1 runs and the server crashes before line 2 runs, the database now shows Riya with ₹1500 and Kabir still with ₹500 — total money in the system dropped from ₹2500 to ₹2000. That missing ₹500 is not a rounding error; it is a bug that, at bank scale, is a financial and legal disaster.

A transaction is the database's answer to this problem: a group of one or more operations that the database treats as a single, indivisible unit of work. You mark where it starts and where it should be permanently saved, like this:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE acc_no = 101;
UPDATE accounts SET balance = balance + 500 WHERE acc_no = 202;

COMMIT;

START TRANSACTION tells the database: "everything from here on is one logical job — do not let anyone else see it half-finished, and do not keep any of it if you can't finish all of it." COMMIT tells the database: "the job is complete — make it permanent." There is a third keyword, ROLLBACK, which tells the database: "abandon this job — undo anything you already did." A well-designed database guarantees that if the crash happens between the two UPDATE lines, it behaves as though it silently ran ROLLBACK for you the moment it restarts — Riya's balance goes right back to ₹2000, and no money is lost or created. When you retry the payment from a fresh transaction, it either fully succeeds or fully fails again, never half of either.

That single guarantee — all-or-nothing — is the first of the four ACID letters. Let's build each one from a scenario, the way a database engineer actually has to reason about them.

Atomicity — an "atom" that cannot be split in its effect

The word atomic originally meant "cannot be cut" in Greek. In transactions it does not mean "instant" — a transaction can take milliseconds or run many statements. It means the two possible outcomes visible to the rest of the world are only all committed or all rolled back. No third, half-done outcome is ever left behind for anyone to see.

Trace it again carefully:

• Before: Riya = ₹2000, Kabir = ₹500 (total ₹2500)
• Line 1 executes: Riya = ₹1500 (total now internally ₹2000, but this is not yet visible or final)
• Crash. Server restarts.
• The DBMS (database management system) checks its log, sees this transaction never reached COMMIT, and automatically undoes line 1: Riya = ₹2000 again.
• Final state seen by every user: Riya = ₹2000, Kabir = ₹500 — exactly as if the payment was never attempted.

Without atomicity, the two UPDATE statements would just be two independent accidents waiting to half-happen. With it, they behave as one unbreakable unit.

Consistency — the database's rules must always hold, before and after

Atomicity alone is not enough. Suppose Riya only has ₹300 in her account and mistakenly tries to send Kabir ₹500. Every real bank database has a rule — a constraint — built directly into the table definition:

CREATE TABLE accounts (
  acc_no  INT PRIMARY KEY,
  name    VARCHAR(50),
  balance DECIMAL(10,2) CHECK (balance >= 0)
);

That CHECK (balance >= 0) is a promise the database makes forever: no account can ever go negative. Now trace Riya's ₹500 transfer from a ₹300 balance:

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE acc_no = 101;
-- attempted new balance: 300 - 500 = -200
-- this violates CHECK (balance >= 0)
-- the DBMS refuses to apply the change and marks the transaction as failed
ROLLBACK;

Consistency means every transaction takes the database from one valid state to another valid state, according to every rule the database owner has defined — constraints like "balance cannot be negative," foreign keys like "you cannot pay an account number that does not exist," and business invariants like "the total money in the system before a transfer must equal the total money after it" (₹2500 in, ₹2500 out — money is only moved, never created or destroyed by a transfer). If completing a transaction would break any of these rules, the database refuses to commit it at all. Notice how consistency and atomicity work together here: it is precisely because the database can cleanly undo line 1 (atomicity) that it is able to enforce the balance rule (consistency) without leaving Riya's account in an illegal, negative state even temporarily.

Isolation — what happens when two transactions run at the same time

Atomicity and consistency protect one transaction from crashes. Isolation protects the database when two or more transactions run at the same time — which, on a real system like IRCTC during Tatkal booking hours, happens thousands of times per second.

Suppose train 12345 has exactly one seat left in coach S4. Two passengers, Aditi and Rohan, both hit "Book" within the same second. Each booking runs roughly as:

SELECT seats_available FROM train WHERE train_no = 12345;
-- if seats_available > 0:
UPDATE train SET seats_available = seats_available - 1 WHERE train_no = 12345;

Now trace what happens if the database lets both transactions read and act without any isolation:

Time  Aditi's transaction (T1)          Rohan's transaction (T2)
t1    READ seats_available -> 1
t2                                       READ seats_available -> 1
t3    1 > 0, proceed to book
t4    UPDATE: seats_available = 1-1 = 0
t5    COMMIT  (Aditi's ticket confirmed)
t6                                       1 > 0 (read at t2, still thinks 1), proceed
t7                                       UPDATE: seats_available = 1-1 = 0
t8                                       COMMIT  (Rohan's ticket confirmed)

Both passengers get a confirmed ticket for the same one seat, because Rohan's transaction read the seat count before Aditi's had committed, and never found out it had gone stale. This is a classic concurrency bug called a lost update: Aditi's decrement was overwritten by Rohan's, and the seat is effectively double-sold.

Isolation is the guarantee that this cannot happen — that even though T1 and T2 physically overlap in time, the final result must look as if one of them ran completely before the other started. A real DBMS enforces this using locks: when T1 reads seats_available intending to update it, it takes a lock on that row. T2's read at t2 is made to wait until T1 finishes and commits. Only then does T2 read the row — and it correctly sees seats_available = 0, so its own 1 > 0 check fails, and Rohan is correctly told the seat is sold out (or placed on a waiting list). The two transactions still both ran "at the same time" from a human's point of view, but the database made them behave as though they happened one after another in some order — that illusion of a clean sequence, even under real concurrency, is exactly what isolation means.

Durability — once "Success" is shown, it must survive anything

The last property answers a narrower but critical question: once the database has said COMMIT succeeded — once your UPI app shows the green tick and "₹500 sent" — can that fact ever be lost?

Durability guarantees no. The instant a transaction commits, its effects are written to non-volatile storage (a disk or SSD, not just the computer's RAM) in a way that survives a crash, a power cut, or the server rebooting a millisecond later. Practically, DBMSs achieve this using a technique called write-ahead logging: before the DBMS ever reports "committed" back to the application, it first writes a durable log record on disk describing exactly what changed. If the power fails one millisecond after your app shows "Success," the running data in memory might be lost — but on restart, the DBMS reads that log and replays the change, reconstructing the exact same final state. Your ₹500 debit and Kabir's ₹500 credit are not "probably fine" — they are guaranteed to exist, because the promise of commit was never made until it was already safely written down.

A common misconception, corrected

Students often assume atomicity means the transaction executes instantaneously — as if "atomic" describes speed. It does not. A transaction can involve dozens of statements and take a noticeable amount of time to run. What atomicity actually guarantees is about the outcome, not the duration: no other part of the system is ever allowed to observe the transaction in a half-finished state, and if it cannot finish entirely, every trace of its partial work is removed. A transaction transferring money between ten accounts in a round-robin chain might take a full second to execute — but atomicity ensures that, from the outside, it looks like either all ten balances changed together at one instant, or none of them changed at all. "Atomic" describes indivisibility of effect, not speed of execution.

How the four properties depend on each other

It helps to see that ACID is not four unrelated rules bolted together — each one is solving a different failure that the others cannot fix on their own:

• Atomicity protects against a transaction stopping halfway (crash mid-transaction).
• Consistency protects the database's own rules and invariants (no negative balances, no orphaned records) — and relies on atomicity to be able to cleanly undo an illegal attempt.
• Isolation protects against damage from two transactions overlapping in time (the IRCTC double-booking case) — a problem atomicity alone does nothing about, since each individual transaction was perfectly atomic on its own.
• Durability protects a transaction's result after it has successfully finished, against crashes that happen later (the power-cut-after-commit case).

The term itself — ACID — was coined by computer scientists Theo Härder and Andreas Reuter in the early 1980s to name exactly this bundle of guarantees, and it remains the standard vocabulary used to describe reliable transaction processing in every major relational database — MySQL, PostgreSQL, Oracle, SQL Server — that powers systems from IRCTC ticketing to UPI settlement to your school's examination result database.

The transaction lifecycle

Every transaction moves through a small number of well-defined states from the moment it begins to the moment it is finally resolved. This is what the database is actually tracking internally as it applies the ACID guarantees:

A transaction becomes Active the moment it begins executing statements. If every statement runs successfully, it briefly enters Partially Committed — every operation has finished, but the DBMS has not yet guaranteed the result is safely on disk. Once that guarantee is met, it moves to Committed, and durability now applies permanently. But if a crash or a broken rule (like the negative-balance check) is detected at any point along the way, the transaction moves to Failed, and the DBMS performs a rollback, undoing every change made so far, landing in the Aborted state — exactly as atomicity promised.

Check your understanding

1. Riya's account has ₹1200. She starts a transaction to send Kabir ₹1500. Using the CHECK (balance >= 0) constraint from this chapter, explain which ACID property stops this transaction from succeeding, and what state (from the diagram) it ends up in.
2. A school's result-upload system runs one transaction per student that both (a) inserts their marks into the results table and (b) updates a class_average table. The server loses power after step (a) completes but before step (b) runs. Which ACID property is responsible for making sure the database does not end up with a student's marks recorded but the class average left stale or half-updated?
3. Two clerks at an IRCTC counter both try to allot the same lower-berth seat within the same second, using the read-then-update logic shown earlier in this chapter. Name the anomaly that occurs if isolation is not enforced, and describe in one sentence how row-locking prevents it.
4. Explain, in your own words, why "atomic" does not mean "instant" — give an example transaction that takes several steps yet is still atomic.

Self-check: (1) Consistency — the CHECK constraint would be violated (balance would become −₹300), so the DBMS rejects the update and the transaction moves to Failed, then Aborted via rollback, leaving Riya's balance unchanged at ₹1200. (2) Atomicity — both the marks insert and the average update must be treated as one unit; on crash, the DBMS rolls back the marks insert too, so no half-finished record is left behind. (3) A lost update (the seat gets double-allotted); locking makes the second clerk's read wait until the first clerk's transaction commits, so the second clerk correctly sees the seat is already taken. (4) A transaction transferring salary to fifty employees at once involves fifty separate updates and may take real time to run, yet remains atomic because no one can observe salaries seven-through-twenty updated while the rest are still pending — the visible result is always all fifty done or none done.

Summary

A transaction is a group of database operations bundled into one logical, indivisible unit, opened with START TRANSACTION and closed with either COMMIT (keep the changes) or ROLLBACK (undo them). The four ACID properties are the guarantees a reliable DBMS makes about every transaction: Atomicity ensures all its operations succeed together or none do; Consistency ensures the database's own rules and invariants are never violated by a committed transaction; Isolation ensures transactions running at the same time do not corrupt each other's results, using mechanisms like row-locking; and Durability ensures that once a transaction commits, its effects survive any later crash, typically via write-ahead logging to disk. Together they are why you can trust that a UPI transfer, an IRCTC seat booking, or a school marks upload behaves correctly even when networks fail, servers crash, and thousands of people click "submit" at the exact same second.

Think About It

Think about this: How would you explain database transactions and acid properties to a friend who has never seen a computer? What real-world analogy would you use? Imagine you had to build a system using these concepts — what would be your first step? Try this: before moving on, write down three things you learned and one question you still have.

Practice Exercises

Now it is time to practice! Complete these challenges to solidify your understanding:

• Exercise 1: Write a short program that demonstrates the core concept from this chapter. Test it with at least 3 different inputs.
• Exercise 2: Find a real-world example where database transactions and acid properties is used in an Indian company (like TCS, Infosys, Flipkart, or ISRO). Write a paragraph explaining the connection.
• Exercise 3: Create a mind-map connecting database transactions and acid properties to at least 3 other topics you have studied.