Transactions and Concurrency: ACID, Isolation, and Locking

Transactions and Concurrency

Transactions are how a database protects correctness when hundreds of requests hit the same rows at the same time. Getting isolation levels and locking wrong doesn't show up in a unit test — it shows up three months into production as a race condition that corrupts an account balance or double-books a seat. This guide covers ACID, every standard isolation level, how locking actually works in InnoDB, and how to reason about deadlocks instead of just retrying and hoping.

1. ACID Properties

START TRANSACTION;
 
UPDATE accounts SET balance = balance - 5000 WHERE id = 1 AND balance >= 5000;
-- Application checks affected row count here; if 0, insufficient funds, ROLLBACK
 
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
 
COMMIT;   -- Atomicity: both updates happen together, or neither does

2. Concurrency Anomalies

Before isolation levels make sense, you need to understand the specific ways concurrent transactions can corrupt what a transaction "sees."

3. Isolation Levels

The SQL standard defines four isolation levels, each permitting a strict subset of the anomalies above in exchange for better concurrency.

-- Set isolation level for the current session (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- ... business logic ...
COMMIT;

How MVCC actually prevents dirty/non-repeatable reads

4. Locking in InnoDB

Isolation levels describe the guarantee; locks are the mechanism that enforces it for writes (and for SELECT ... FOR UPDATE reads).

Lock types

-- Explicit locking read: reserve rows for update within a transaction
START TRANSACTION;
 
SELECT quantity FROM inventory
WHERE product_id = 55 AND warehouse_id = 1
FOR UPDATE;   -- exclusive lock held until COMMIT/ROLLBACK
 
-- Application checks quantity >= requested amount here
UPDATE inventory SET quantity = quantity - 3
WHERE product_id = 55 AND warehouse_id = 1;
 
COMMIT;   -- lock released

Optimistic vs pessimistic locking

-- Optimistic locking: no lock held, conflict detected at write time
-- Table has a `version` column
UPDATE products
SET price_cents = 45000, version = version + 1
WHERE id = 55 AND version = 7;
-- If another transaction already bumped version to 8, this affects 0 rows.
-- Application detects 0 rows affected and retries or surfaces a conflict.

5. Deadlocks

A deadlock happens when two transactions each hold a lock the other needs, and neither can proceed.

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... (some delay) ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- may deadlock
COMMIT;
 
-- Transaction B (running concurrently, opposite order)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- ... (some delay) ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;    -- may deadlock
COMMIT;

Preventing deadlocks

// Typical application-level retry pattern for deadlocks (pseudocode structure)
int attempts = 0;
while (attempts < 3) {
    try {
        transactionTemplate.execute(status -> {
            accountRepository.debit(fromId, amount);
            accountRepository.credit(toId, amount);
            return null;
        });
        break;
    } catch (DeadlockLoserDataAccessException e) {
        attempts++;
        if (attempts == 3) throw e;
        sleepWithJitter(attempts);
    }
}

6. Transaction Scope in Application Code

// BAD: external call inside the transaction boundary
@Transactional
public void placeOrder(OrderRequest request) {
    Order order = orderRepository.save(toOrder(request));
    paymentGateway.charge(order);   // network call while DB locks are held!
    inventoryRepository.decrement(order.getItems());
}
 
// BETTER: keep the transaction to DB work only; use an outbox for the side effect
@Transactional
public void placeOrder(OrderRequest request) {
    Order order = orderRepository.save(toOrder(request));
    inventoryRepository.decrement(order.getItems());
    outboxRepository.save(new OutboxEvent("PAYMENT_REQUESTED", order.getId()));
    // A separate async worker reads the outbox and calls paymentGateway.charge(...)
    // outside of any open DB transaction, with its own retry logic.
}

Key takeaways

• ACID's four properties aren't abstract — atomicity and isolation are what make concurrent writes to the same row safe; durability is what a COMMIT actually promises.
• MySQL/InnoDB defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED — never assume the default, set isolation explicitly for correctness-critical code.
• InnoDB's MVCC lets readers see a consistent snapshot without blocking writers, which is why strong isolation doesn't have to mean poor read throughput.
• Use SELECT ... FOR UPDATE (pessimistic) for short, high-contention critical sections; use a version column (optimistic) when there's human think-time between read and write.
• Deadlocks are normal under concurrency, not a sign of a broken system — the fix is application-level retry logic plus consistent lock ordering, not trying to eliminate them entirely.
• Never hold a database transaction open across a network call — it's one of the most common causes of cascading production outages under load.
• An atomic conditional UPDATE ... WHERE quantity >= ? is often simpler and safer than SELECT ... FOR UPDATE followed by a separate UPDATE.
• Keep transactions as short as possible; every statement inside BEGIN/COMMIT extends how long your locks block everyone else.

Interview Questions

• Explain each of the four ACID properties with a concrete example of what breaks if it's missing.
• What is a dirty read, and which isolation level prevents it?
• What is the difference between a non-repeatable read and a phantom read?
• Why do MySQL and PostgreSQL have different default isolation levels, and why does that matter for portability?
• How does MVCC allow readers to avoid blocking on writer locks?
• What is the difference between a record lock, a gap lock, and a next-key lock in InnoDB?
• When would you choose optimistic locking over pessimistic locking? Give a concrete example of each.
• Walk through how a lost update happens with two concurrent SELECT + UPDATE sequences, and how you'd prevent it.
• What causes a deadlock, and how does InnoDB resolve one once detected?
• What is the single most effective technique for preventing deadlocks in a multi-row transactional workflow?
• Why is it dangerous to call an external HTTP API inside an open database transaction?
• How would you implement a reliable "charge payment after order is saved" flow without holding locks across the payment call?
• What does SELECT ... FOR UPDATE actually do, and what's an alternative single-statement pattern that avoids holding a lock across a round trip?
• Why might lowering isolation from REPEATABLE READ to READ COMMITTED reduce deadlocks, and what do you give up by doing so?