What is ATOMIC SQL UPDATES?

Excellent.
Day 7 is one of the most practical days — this is where we eliminate an entire class of production bugs by changing how we write SQL.
Today’s focus:
Avoid Read → Modify → Write bugs using ATOMIC SQL UPDATES
? DAY 7 — ATOMIC SQL UPDATES
1️⃣ The Core Problem (MOST COMMON BUG)
❌ Read → Modify → Write Pattern
Account acc = repo.findById(id);
acc.setBalance(acc.getBalance() - amount);
repo.save(acc);
Why this is dangerous
Two threads can read same value
Both modify
Last write wins
Lost update
Even with transactions, this can fail under concurrency.
2️⃣ Atomic Update (The Fix)
Let the database do the math
✅ Correct SQL
UPDATE account
SET balance = balance - :amount
WHERE id = :id AND balance >= :amount;
✔ Single statement
✔ Atomic
✔ No lost updates
✔ No negative balance
3️⃣ Production-Grade Withdraw Code
@Transactional
public void withdraw(Long accountId, int amount) {

    int rows = repo.withdraw(accountId, amount);

    if (rows == 0) {
        throw new InsufficientFundsException();
    }

    ledgerRepo.save(
        new LedgerEntry(UUID.randomUUID(), accountId, "WITHDRAW", amount)
    );
}
Repository
@Modifying
@Query("""
   UPDATE Account a
   SET a.balance = a.balance - :amount
   WHERE a.id = :id AND a.balance >= :amount
""")
int withdraw(Long id, int amount);
4️⃣ Why This Is Better Than Locking
Approach
Locks
Safety
Performance
SELECT FOR UPDATE
Yes
High
Medium
Atomic UPDATE
Implicit
High
High
? DB internally locks the row for the statement duration
5️⃣ Deposit (Same Pattern)
UPDATE account
SET balance = balance + :amount
WHERE id = :id;
✔ Always safe
✔ No lock ordering needed
6️⃣ Why This Avoids Deadlocks
Single row
Single statement
No multiple locks
No circular wait
Deadlock-free by design.
7️⃣ Combine with Idempotency (VERY IMPORTANT)
UNIQUE(txn_id)
If retry happens:
UPDATE runs again
Ledger insert fails
Balance remains correct
8️⃣ When NOT to Use Atomic Updates
❌ Complex business rules
❌ Multiple dependent rows
❌ Cross-entity invariants
Then use:
Pessimistic locking
Consistent lock ordering
9️⃣ Real Bank Systems Do This
Balance = cached value
Ledger = truth
Balance updated atomically
Ledger append is immutable
? Mental Model (MEMORIZE)
If you can express your update in one SQL statement, do it.
✅ Day 7 Summary
✔ Avoid read-modify-write
✔ Use atomic SQL
✔ DB guarantees correctness
✔ Faster and safer
✔ Less code, fewer bugs
? Where You Are Now
You now know:
Transactions
Idempotency
Deadlocks
Isolation
Locking
Atomic updates
This is production backend mastery.
? Day 8 (Next)
Designing a Ledger-Based System (How Banks Really Store Money)