How Well Do You Understand Transactions? Debunking 8 Common Myths

My second favourite thing in an interview — right after the classic “A user browses to X in their browser; describe the request lifecycle in detail” — is the section of high-level database questions. This is where everything depends on the interviewer’s level of meticulousness. A profound, detailed response can either impress or paint you as that guy — you know, the stuffy one who knows too much and overcomplicates everything. But here’s the thing: database transactions are a minefield of misconceptions, especially when you’re expected to give short, snappy answers in an interview. Today, I want to dive into ten common myths about database transactions, exploring why they’re oversimplified half-truths in interview contexts, and why, in reality, they’re often complete nonsense.

Disclaimer: Shit might hit the fan. The author (me) didn’t specify a particular database or exact definitions upfront, so behavior might differ across systems like PostgreSQL, MySQL, or MongoDB

Myth 1: A Transaction Always Completes Successfully

Short answer: “Sure, transactions are designed to succeed!”

Why it’s true in general: In an ideal spherical vacuum, a transaction commits all changes when done.

Reality check: Nope, not even close.

Transactions can fail due to crashes, timeouts, or deadlocks. If the server dies mid-transaction, rollback isn’t guaranteed, and data can end up inconsistent.

Imagine you’re transferring $100 between bank accounts — database says “debit account A,” but the server dies before “credit account B” happens. Transaction fails, and you’re left cursing the gods of ACID.

While WAL (Write-Ahead Logging) is designed to prevent partial transactions from being committed during recovery, real-world implementations can have edge cases. Database crash recovery depends on log integrity and proper fsync operations. If the transaction log itself becomes corrupted or truncated during the failure, the database might fail to identify and roll back the partial transaction. However, in well-configured systems (e.g., with fsync enabled), this risk is minimized.

def transfer_money(from_account, to_account, amount)
  ActiveRecord::Base.transaction do
    from_account.update!(balance: from_account.balance - amount)
    to_account.update!(balance: to_account.balance + amount)
  end
end

If the database connection drops after from_account updates but before to_account, the transaction should roll back — ActiveRecord relies on the database’s rollback mechanism. But if the server crashes hard (e.g., power loss) right after the first update hits the transaction log but before the second, the log might be incomplete.

Myth 2: A Transaction Locks Data Until It Completes

Database anomalies depending on the transaction isolation levels ( jlvbcoop )

Short answer: “Yes, transactions lock data to ensure consistency.”

Why it’s true in general: Locking ensures isolation — a safe, solid answer.

Reality check: Not always. It depends on the isolation level. With READ COMMITTED, locks might drop after a read, letting others check the data before the transaction’s done. SERIALIZABLE keeps locks tight until the end. So, no, data isn’t always locked until the end — sometimes it’s a free-for-all.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

FOR UPDATE grabs a write lock. In READ COMMITTED, another session can read id=1’s committed value before this transaction ends — say, post-SELECT but pre-UPDATE. If the server dies mid-UPDATE, the lock vanishes on crash, and nothing commits unless the log’s written. SERIALIZABLE would block that read — stricter rules apply.

Myth 3: Transactions Are Always Atomic

Short answer: “Yep, atomicity is a pillar of ACID!”

Why it’s true in general: Atomicity — everything happens or nothing does — is a defining trait of transactions.

Reality check: They’re supposed to be atomic, but reality’s messier. Atomicity fails in crashes or distributed systems if commit/rollback doesn’t fully execute.

Atomicity relies on the transaction log’s integrity. In a crash, the database uses the log to ensure either all changes are applied or none are. However, if the log is incomplete or corrupted, recovery might leave partial changes.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the server crashes after the first UPDATE logs but before the second, and the log’s incomplete, recovery might leave id=1 down 100 with no change to id=2. This happens if the crash hits post-log-write for the first but pre-log-write for the second. Atomicity lives or dies by the log’s integrity.

Myth 4: If an Operation Completes Successfully, All Data Has Been Updated

Short answer: “Yes, success means all changes are done.”

Why it’s true in general: In a simple world, a successful transaction implies all intended updates are locked in.

Reality check: Define “operation.” If we mean the whole transaction, a commit should mean all data’s updated — but “should” isn’t “does.” If “operation” is one query in a transaction, others could still fail, triggering a rollback. Plus, even after a commit, data might stay in memory before hitting disk (dirty pages) . Success isn’t as final as it sounds. Committed data might stay in memory, not disk. A crash post-commit can lose it if the WAL isn’t durable yet. In databases like PostgreSQL, with asynchronous commit, there’s a small window where committed data hasn’t been flushed to disk. Proper configuration (synchronous_commit=on) minimizes this risk, ensuring durability at the cost of performance.

BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1;
COMMIT;

COMMIT succeeds, but the data might sit in the buffer. If the server dies post-COMMIT but pre-flush (no fsync), and the WAL isn’t durable yet, the update’s gone on restart. This window’s small — milliseconds — but real.

Myth 5: Atomicity Always Guarantees No Conflicts

Short answer: “Atomicity keeps everything conflict-free!”

Why it’s true in general: Atomicity’s about indivisibility, so it’s easy to assume it avoids conflicts.

Reality check: Ha, nope. Atomicity ensures a transaction’s all-or-nothing, but it doesn’t stop concurrent transactions from clashing. Deadlocks, lost updates — those are isolation problems, not atomicity’s job. Two atomic transactions can still fight over the same row. Atomicity is per-transaction, while isolation governs how transactions interact. Even with atomic transactions, poor isolation can lead to anomalies like dirty reads or lost updates.

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;

If another transaction locks id=2 first, then tries id=1, we’re deadlocked. If the server dies mid-deadlock, one might commit partially if the log’s written up to an UPDATE — timing matters. Atomicity’s per-transaction, not system-wide.

Myth 6: Write Operations to Caches or File Systems Are Also Atomic

Short answer: “Uh, yeah, atomicity extends everywhere (and covers everything)”

Why it’s true in general: You might assume transaction atomicity covers related systems.

Reality check: Depends entirely on the system. Database transactions might be atomic, but caches and file systems? Some caches (like Redis with transactions) can pull it off, but most don’t worry. File systems vary — some support atomic writes, others leave you with half-written files if power cuts out. Atomicity stops at the database entrance unless explicitly built elsewhere. Redis, for example, offers command-level atomicity, but multi-command scripts aren’t transactional unless wrapped in a MULTI/EXEC block. Even then, if the server crashes mid-script, partial updates can stick.

If Redis crashes mid-SET, the write might truncate or corrupt — say, after writing half the value. No rollback here; Redis’s atomicity is command-level, not transaction-level. If the server dies during a multi-command script, partial updates stick.

Myth 7: Rollback Is Always Possible

Interview short answer: “You can always roll back a transaction”

Why it’s true in general: Rollback’s a cornerstone of ACID’s “abort” option.

Reality check: Not in every universe. NoSQL systems? Many don’t even offer it. Commit’s final, or failures leave you with manual cleanup. Physical failures (disk crash mid-transaction) can also make rollback a fantasy. MongoDB, for instance, only supports rollbacks in replica sets (post-4.0) . In standalone mode, there’s no rollback — writes stick. Even in relational databases, if the server crashes mid-transaction and the log is partial, recovery might commit partial changes.

Mongoid::Session.default.with_transaction do
  User.create!(name: "Alice")
end

MongoDB only rolls back in replica sets. If the server dies mid-transaction and the session drops — say, after create! ( ), but before commit — writes might stick. Standalone mode? No rollback.

BEGIN;
INSERT INTO users (name) VALUES ('Alice');
COMMIT;

Rollback works pre-COMMIT in PostgreSQL. But if the server dies mid-INSERT and the log’s partial — say, after writing the row but before transaction end — recovery might leave it committed. NoSQL like MongoDB might not even offer rollback; writes stick post-failure.

Myth 8: Atomic Operations Are Always Fast

Short answer: “Yes!”

Why it’s true in general: It’s a feel-good assumption. Golang creators wouldn’t call a package containing fast sync primitives — atomic.

Reality check:

Atomicity can slow down with big data or contention. Atomic doesn’t mean fast. Big data or slow resources (disks, networks) can drag it out. Speed’s not part of the deal. An atomic operation locking a million rows or syncing across a slow network can crawl. Atomicity’s about correctness, not performance. A complex transaction can be atomic and still take ages. In databases, atomic operations often require locks, which can block other transactions and degrade performance, especially with large datasets or high contention. For example, updating a million rows in a single transaction will hold locks for the duration, potentially causing bottlenecks.

BEGIN;
UPDATE users SET last_login = NOW();
COMMIT;

This locks the table atomically. With millions of rows, it crawls — locks block reads/writes until done. If the server dies mid-UPDATE, partial writes might commit if the log’s written up to that point.

Conclusion: