A Beginner's Guide to Database Transactions
@arunrktech

✍️ Translate this blog into Hindi, Tamil or Telugu
What really happens when two people click "Buy" at the same moment, and how your database keeps the data honest.
Picture a holiday season sale. Thousands of shoppers hit checkout in the same second, and only five units of a popular item are left. Somewhere behind that button, a database has to decide who gets the item, who gets to see "sold out," and how to make sure nobody gets charged for an item that does not exist.
In my years building order management systems, the bugs that hurt the most were rarely the dramatic crashes. They were the quiet ones: two things happening at the same time and stepping on each other. An order that showed as paid but never reserved stock. An inventory count that drifted by one every busy weekend. These problems are almost always about transactions and concurrency.
What is a transaction?
Computers fail all the time. Applications crash halfway through a task, networks drop, power goes out. If a database is in the middle of writing several related changes when that happens, you can end up with half a record, an index that no longer matches its table, or a reference pointing to nothing.
🛍️✨ From the author · ByteStoreWhat is meant by enterprise ready?₹500View offer →A transaction is the database's answer. It groups several reads and writes into one unit of work and promises one of exactly two outcomes:
- Commit: every change in the group is saved.
- Abort: every change in the group is thrown away, as if nothing happened.
Think of moving money between two jars. You take $100 out of one and put it in the other. If you drop the cash on the floor in between, you do not want the first jar to be short while the second never got it. You want to start over cleanly. That is a transaction.
A little history: transactions became formal with IBM's System R in 1975. In the late 2000s, many NoSQL databases dropped them in the name of scale, on the belief that transactions and scalability could not live together. That belief has since been disproven. Modern distributed databases offer both, as long as isolation is designed carefully.
ACID
You will see the acronym ACID everywhere when you are reading through any datarelated material. Here is what the acronym means:
- Atomicity: All or nothing. If anything fails mid way, the database rolls back every change. A better name would be "abortability."
- Consistency: Your business rules stay true, such as "debits must equal credits." This one is mostly the application's job. The database provides the other three letters, and your code uses them to keep its own rules intact.
- Isolation: Transactions running at the same time do not see each other's unfinished work. Each one runs inside its own protective bubble.
- Durability: Once the database says "committed," the data survives a crash. This used to mean writing to disk. In the cloud it usually means copying data to several machines in separate locations.
One record update versus many records
Some changes touch a single value. Imagine a bank ticket counter sitting at 42, and two users both want to get the next ticket from two different dispensers.
Without isolation, both read 42, both add one, and both write 43. One increment simply disappears. This is called a lost update. Databases solve the single value case with atomic operations like "increment this counter" or "compare and set," so the second user reads 43 and writes 44.

Most real work touches several records at once. Take an email app that keeps an unread counter next to the inbox. Receiving a message means two steps: add the email, then bump the counter. If another process reads between those two steps, it sees a new email but an unread count of zero. A multi-record transaction makes both changes appear together, or not at all.
In an order management system, the same pattern shows up everywhere. Placing an order means creating the order, reserving inventory, and recording the payment. If only one or two of those land, you get exactly the drifting counts I mentioned earlier.
The common problems
Dirty read: You read data another transaction has written but not yet committed. If that transaction aborts, you acted on data that never officially existed. It's like a server picking up a plate from a full-course meal before the chef could add all that comes with the meal.
Dirty write: You overwrite data another transaction has written but not yet committed. Picture Alice and Bob buying the same used car. Alice's transaction sets the buyer to Alice. Before she commits, Bob's transaction overwrites it with Bob, and his invoice goes out. The records now disagree about who bought the car.
Read skew: You read several things, but at slightly different moments, so the picture you see never actually existed. Consider two accounts each hold $500, so the total is $1,000. An auditor reads Account 2 and sees $500. Then a transfer moves $100 from Account 1 to Account 2 and commits. The auditor now reads Account 1 and sees $400. Their total says $900. No money vanished, but the report says it did.

Write skew: Two transactions read the same condition, both decide it is safe, and each writes to a different record. Since they never touch the same row, simple row locks do not catch it. Classic examples: two people book the same meeting room at once, or two signups claim the same username. In an OMS, two orders each check "is at least one unit available?", both see yes, and each inserts its own reservation. You have just oversold.

A warning: vendors use these names inconsistently. PostgreSQL's "Repeatable Read" is actually snapshot isolation. Oracle's "Serializable" is also snapshot isolation, not true serializability. Always check what your database means, not just what it says.
How databases keep order
🛍️✨ From the author · ByteStoreResume review within 48 hours₹200View offer →There are three main strategies. A grocery store makes a good analogy.
Actual serial execution: one checkout lane. Run every transaction one after another on a single thread. No overlap, so no anomalies. It works only when transactions are tiny and fast, the data fits in memory, and the write volume fits on one CPU core. Logic usually lives in stored procedures so the database never waits on the application over the network.
Two phase locking (2PL): locks on every shelf. Readers lock what they read and writers lock what they write. Readers block writers and writers block readers. It guarantees serializability and was the standard for about 30 years, but under heavy load it means long waits, lower throughput, and deadlocks.
Snapshot isolation : an image of the data. When a transaction starts, it gets a consistent snapshot of the data, powered by a technique called MVCC (multiversion concurrency control). Readers never block writers and writers never block readers.
Serializable Snapshot Isolation (SSI) : adds a check at commit time: if another transaction changed something this one relied on, the later one is aborted and retried. You get full correctness with far less waiting than 2PL.
Retries:
Aborted transactions need to be retried, but retries cause other problems.
• Lost acknowledgments: The transaction commits, but the network drops before the client hears back. The client retries and charges the card twice. This is why checkout pages say "click once."
• Retry storms : A busy database starts timing out. Hundreds of clients retry at the same instant, making it busier, until it collapses.
• Permanent errors: Retrying a duplicate key violation with the same input will fail forever.
To fix the retry issues cause:
1. Exponential backoff : Wait longer after each failure, plus a random amount, so clients do not retry in lockstep.
2. Retry caps: Give up after a few attempts and fail gracefully.
3. Idempotency keys: Send a unique ID with each request. If the server has already committed that ID, it returns the saved result instead of running the write again. In order management, an idempotency key per payment capture is the single cheapest insurance against double charges.
For better understing take a look at the video explainer :
🛍️ Arun Kumar's shelf
Courses, cohorts and more from the authorGet Arun Kumar's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
More from Arun Kumar

Big Data and Smart Cities: Last Mile Delivery and Urban Logistics
Connecting city data to retail fulfilment: the OMS view

When AI Found a Decade Old Bug in 70% of the Web?
What PHP2Shell Teaches New Developers ?
#container
Two Trucks: Explaining VMs and Containers
VMs and Containers

What If You Knew the weather before it started changing?
A new AI tool can now spot major storms and weather shifts weeks ahead, giving retailers and supply chains time to stock the right products and keep goods moving.

💬 Comments (0)
Top · NewestSign in to comment