ACID Properties

Property InnoDB Implementation
Atomicity All statements commit or all roll back
Consistency Constraints enforced; invalid states rejected
Isolation MVCC + locks prevent interference
Durability Redo log flushed to disk on commit

Only InnoDB supports full ACID transactions. MyISAM does not — never use MyISAM for transactional data.

Basic Transaction Flow 

  START TRANSACTION;
-- or BEGIN; or SET autocommit = 0;

UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Check for errors and ROW_COUNT()
COMMIT;
-- Or: ROLLBACK;
  
  SET autocommit = 0;
INSERT INTO audit_log (action) VALUES ('transfer started');
-- ... business logic ...
COMMIT;
SET autocommit = 1;

With autocommit = 1 (default), each statement is its own transaction.

Isolation Levels 

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- Other transactions may have committed changes visible here (RC)
COMMIT;
Level Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED No Possible Possible
REPEATABLE READ (default) No No Prevented*
SERIALIZABLE No No No

*InnoDB uses next-key locking at REPEATABLE READ to prevent phantom reads in most cases.

READ COMMITTED is common in Oracle-mode applications. REPEATABLE READ is MySQL default — understand which your ORM expects.

Row-Level Locking 

  START TRANSACTION;

SELECT * FROM products WHERE id = 5 FOR UPDATE;
-- Exclusive lock held until COMMIT

UPDATE products SET stock = stock - 1 WHERE id = 5;
COMMIT;
Lock Clause Behavior
FOR UPDATE Exclusive lock — blocks other FOR UPDATE and writes
FOR SHARE (LOCK IN SHARE MODE) Shared lock — blocks writes, allows reads
SKIP LOCKED Skip rows locked by others (job queues)
NOWAIT Fail immediately if lock unavailable

Job Queue Pattern 

  START TRANSACTION;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs SET status = 'processing' WHERE id = @job_id;
COMMIT;

MVCC (Multi-Version Concurrency Control)

InnoDB keeps multiple row versions. Readers do not block writers; writers do not block readers (except on conflicting locks). Each transaction sees a snapshot based on isolation level and start time.

  -- Transaction A (REPEATABLE READ)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- Transaction B commits: UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;  -- Still 1000 in RR
COMMIT;

Deadlocks

Deadlocks occur when transactions lock resources in different orders:

  -- Transaction A: lock row 1, then row 2
-- Transaction B: lock row 2, then row 1
-- InnoDB detects deadlock, rolls back one transaction

Application handling:

  for attempt in range(3):
    try:
        execute_transfer(from_id, to_id, amount)
        break
    except DeadlockError:
        time.sleep(0.1 * attempt)

Prevention: access rows in consistent order; keep transactions short; avoid user interaction inside transactions.

  SHOW ENGINE INNODB STATUS\G
-- Read LATEST DETECTED DEADLOCK section

SAVEPOINT 

  START TRANSACTION;
INSERT INTO audit (action) VALUES ('step 1');
SAVEPOINT sp1;
INSERT INTO audit (action) VALUES ('step 2');
ROLLBACK TO sp1;  -- undo step 2 only
COMMIT;           -- step 1 committed

Transaction Size and Duration

Long transactions are dangerous:

  • Hold locks → block other sessions
  • Prevent purge of old MVCC versions → bloat undo log
  • Increase deadlock probability
  -- Bad: transaction open while calling external API
START TRANSACTION;
SELECT ... FOR UPDATE;
-- ... HTTP call to payment gateway (5 seconds) ...
UPDATE ...;
COMMIT;

-- Good: prepare outside, lock briefly inside
-- Validate payment first, then:
START TRANSACTION;
UPDATE accounts ...;
INSERT INTO payments ...;
COMMIT;

Best Practices

  1. Keep transactions as short as possible
  2. Access tables in consistent order across all code paths
  3. Use appropriate isolation level — do not default to SERIALIZABLE
  4. Implement retry logic for deadlocks and lock wait timeouts
  5. Never mix transactional and non-transactional engines in one transaction

Common Mistakes

Mistake Consequence
No transaction for multi-step financial ops Partial updates, data corruption
SELECT without lock before update Lost updates under concurrency
Long-running report in RR holding snapshot Undo log growth, slow purge
Assuming autocommit off globally Connection pool leaks transaction state

Troubleshooting

Lock wait timeout:

  SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';  -- default 50s
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

Transaction not rolling back on error: Ensure application catches exceptions and calls ROLLBACK — do not rely on connection drop alone in pooled environments.

Production Scenario

A ticket booking system uses SELECT ... FOR UPDATE on available seats within a 200ms transaction. If ROW_COUNT() = 0 after update (race lost), the application returns “seat taken” and retries with the next available seat. Deadlock retries are capped at three attempts with exponential backoff.

Transactions are the mechanism that makes concurrent OLTP applications trustworthy — use them wherever consistency matters.