In databases, transactions are one of the most important concepts for ensuring data correctness, especially when multiple users are working at the same time.
If transactions are not understood properly, it becomes very difficult to understand problems like write conflicts, lost updates, and data inconsistency.
This tutorial explains:
- What a transaction is
- Why transactions are needed
- How write conflicts happen
- How databases solve these problems
1. What is a Transaction?
A transaction is a group of SQL operations that are executed as a single unit.
Either everything succeeds, or nothing is applied.
This is called the ACID principle, especially the A (Atomicity) part.
Basic example
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;
Meaning:
- Both updates happen together
- If something fails → the database can rollback everything
2. Why Transactions Are Important
Without transactions, databases can end up in an inconsistent state.
Example problem:
Imagine a bank transfer:
- Money is removed from Account A
- System crashes before adding it to Account B
Result:
- Money disappears ❌
Transactions prevent this by ensuring:
- Either both steps succeed
- Or none of them happen
3. What is a Write Conflict?
A write conflict (also called lost update) happens when:
Two transactions read the same data and then overwrite each other’s changes.
This happens in concurrent systems, where multiple users access the database at the same time.
4. Step-by-step Write Conflict Example
Initial database state
accounts:
id | balance
1 | 1000
Two users are updating this account at the same time.
- Transaction A → subtract 100
- Transaction B → subtract 200
Expected result:
1000 - 100 - 200 = 700
Step 1: Both transactions read the same value
Transaction A:
SELECT balance FROM accounts WHERE id = 1; -- 1000
Transaction B:
SELECT balance FROM accounts WHERE id = 1; -- 1000
Key idea:
Both transactions are working with the same snapshot (1000).
Step 2: Both compute new values locally
- A calculates: 1000 - 100 = 900
- B calculates: 1000 - 200 = 800
At this point, nothing is written yet.
Step 3: Transaction B writes first
UPDATE accounts
SET balance = 800
WHERE id = 1;
Database becomes:
id | balance
1 | 800
Step 4: Transaction A writes afterward
UPDATE accounts
SET balance = 900
WHERE id = 1;
Database becomes:
id | balance
1 | 900 ❌ wrong final result
5. What Went Wrong?
We expected:
700
But got:
900
Why?
Because:
- Both transactions read the same old value (1000)
- Both computed independently
- The last write overwrote the previous one
👉 This is called a Lost Update (Write Conflict)
6. Why Write Conflicts Are Dangerous
Write conflicts can cause serious real-world issues:
- Bank balances become incorrect
- Inventory systems sell more items than available
- Booking systems double-book seats
- User data gets overwritten
7. How Databases Prevent Write Conflicts
Databases use locking and isolation levels.
Solution 1: Row Locking
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
What this does:
- Locks the row
- Prevents other transactions from modifying it
Correct execution flow:
Transaction A locks row
- Reads 1000
- Calculates 900
- Updates and commits
Transaction B waits
- Only starts after A finishes
- Reads updated value (900)
- Calculates 700
- Updates safely
Final correct result:
id | balance
1 | 700
8. Key Concepts Summary
Transaction
A group of operations treated as a single unit.
Write Conflict
When two transactions overwrite each other's updates.
Lost Update Problem
When one update is overwritten due to concurrent writes.
Solution
- Locking (
FOR UPDATE) - Isolation levels
- Proper transaction design
9. One-Sentence Summary
A write conflict happens when multiple transactions read the same data and overwrite each other’s updates because the database does not coordinate concurrent writes.
“The problem is not reading the data — it’s multiple writes happening without coordination.”
Top comments (0)