MySQL Transactions & ACID Interview Questions

BEGIN (or START TRANSACTION) starts a transaction grouping SQL statements. COMMIT saves all changes permanently, while ROLLBACK reverts all changes to the state before BEGIN. In MySQL, autocommit is enabled by default, so each statement auto-commits unless explicitly disabled.

-- Basic transaction
BEGIN;
    INSERT INTO orders (customer_id, total) VALUES (1, 100);
    INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 5);
COMMIT;  -- Both inserts saved

-- Rollback on error
BEGIN;
    UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 5;
    INSERT INTO sales_log (product_id, quantity) VALUES (5, 10);
    -- Error detected!
ROLLBACK;  -- Inventory update is reverted

-- Disable autocommit
SET autocommit = 0;
INSERT INTO users (name) VALUES ('John');  -- Not committed yet
ROLLBACK;  -- Changes are reverted
INSERT INTO users (name) VALUES ('Jane');
COMMIT;  -- Now it's saved

-- Re-enable autocommit
SET autocommit = 1;

Why it matters: Understanding transaction control ensures data consistency and is fundamental to database reliability.

Real applications: Multi-step operations where all-or-nothing semantics are required.

Common mistakes: Forgetting to commit changes, not handling errors properly in transactions.

Isolation levels control how concurrent transactions interact. READ UNCOMMITTED (dirty reads allowed), READ COMMITTED (prevents dirty reads), REPEATABLE READ (default in MySQL, prevents dirty and phantom reads), and SERIALIZABLE (strictest, full serialization). Higher isolation levels provide more consistency but reduce concurrency.

-- View current isolation level
SELECT @@transaction_isolation;  -- default: REPEATABLE-READ

-- Set isolation level for session
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Isolation levels comparison
-- READ UNCOMMITTED: Allows dirty reads (reading uncommitted data)
-- Concurrency: HIGH | Consistency: LOW

-- READ COMMITTED: Prevents dirty reads, allows phantom reads
-- Concurrency: HIGH | Consistency: MEDIUM

-- REPEATABLE READ: Prevents dirty and phantom reads
-- Concurrency: MEDIUM | Consistency: HIGH

-- SERIALIZABLE: Full serialization, acts like serial execution
-- Concurrency: LOW | Consistency: HIGHEST

-- Global isolation level
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Why it matters: Choosing the right isolation level balances data consistency with application concurrency.

Real applications: High-traffic systems choose READ COMMITTED for performance, financial systems use SERIALIZABLE for safety.

Common mistakes: Not understanding concurrency implications of different levels, using SERIALIZABLE everywhere.

Dirty reads occur when reading uncommitted data from another transaction. Non-repeatable reads happen when a row changes between two reads within a transaction. Phantom reads occur when new rows appear in result set between queries. These problems are prevented at different isolation levels, affecting both consistency and performance.

-- Dirty Read Example (allowed at READ UNCOMMITTED)
-- Transaction 1:
BEGIN;
UPDATE employee SET salary = salary + 1000 WHERE id = 1;
-- Transaction 2 (before commit):
SELECT salary FROM employee WHERE id = 1;  -- Sees uncommitted increase!
ROLLBACK;  -- Transaction 1 rolls back

-- Non-Repeatable Read Example (allowed below REPEATABLE READ)
-- Transaction 1:
BEGIN;
SELECT salary FROM employee WHERE id = 1;  -- Result: 50000
-- Transaction 2:
UPDATE employee SET salary = 60000 WHERE id = 1;
COMMIT;
-- Transaction 1:
SELECT salary FROM employee WHERE id = 1;  -- Result: 60000 (different!)

-- Phantom Read Example (allowed below SERIALIZABLE)
-- Transaction 1:
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Result: 5
-- Transaction 2:
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
-- Transaction 1:
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Result: 6 (phantom!)
COMMIT;

Why it matters: Understanding these problems helps choose appropriate isolation levels and design transaction logic.

Real applications: Financial systems prevent all three, caching systems may tolerate them for performance.

Common mistakes: Assuming REPEATABLE READ prevents all anomalies, not understanding level differences.

Deadlocks occur when two transactions lock resources in opposite order, each waiting for the other. Transaction 1 locks A and waits for B while Transaction 2 locks B and waits for A—neither can proceed. MySQL detects deadlocks and chooses a victim transaction to rollback. Applications must retry rolled-back transactions.

-- Deadlock Example
-- Connection 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Connection 2 (simultaneously):
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- One connection gets "ERROR 1213: Deadlock found"

-- Prevent Deadlocks: Lock resources in consistent order
-- ALWAYS lock account 1 then account 2
-- Connection 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Connection 2 (same order):
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Handle deadlock with retry logic
DEADLOCK_RETRIES = 3;
for i in range(DEADLOCK_RETRIES):
    try:
        BEGIN;
        // Execute transaction
        COMMIT;
        break;
    except DeadlockError:
        ROLLBACK;
        if i == DEADLOCK_RETRIES - 1:
            raise;

Why it matters: Deadlock prevention and handling is essential for reliable multi-user systems.

Real applications: Financial systems, inventory management, booking systems.

Common mistakes: Not implementing retry logic, locking resources in inconsistent order.

Shared locks (read locks) allow multiple readers but block writers. Exclusive locks (write locks) block both readers and writers. Locks can be row-level (most common in InnoDB) or table-level. MySQL uses automatic locking—SELECT uses shared locks, UPDATE/DELETE use exclusive locks depending on isolation level and transaction state.

-- Shared Lock (read lock) - multiple connections can read
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
-- Other transactions can also read
-- But cannot UPDATE or DELETE unless they also use LOCK IN SHARE MODE

-- Exclusive Lock (write lock) - only one connection accesses
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Other transactions blocked from reading or writing this row

-- Lock types in InnoDB
-- Row-level locks: Default, most concurrent
-- Table-level locks: Used for specific operations

-- AUTO-INC locks: Handle auto-increment generation
-- Gap locks: Prevent phantom reads at higher isolation levels

-- View current locks
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- Set lock timeout
SET innodb_lock_wait_timeout = 50;  -- Seconds before timeout

Why it matters: Understanding lock types helps prevent deadlocks and optimize transaction design.

Real applications: High-concurrency systems need row-level locks, critical sections use FOR UPDATE.

Common mistakes: Holding locks too long, not understanding lock escalation.

Savepoints allow rolling back to a specific point within a transaction without reverting the entire transaction. They're useful for complex operations where some parts might fail. SAVEPOINT creates a marker, ROLLBACK TO reverts to it, RELEASE SAVEPOINT deletes it. Savepoints enable partial transaction recovery.

-- Savepoint usage
BEGIN;
    INSERT INTO users (name) VALUES ('John');
    SAVEPOINT sp1;
    
    INSERT INTO user_profiles (user_id, bio) VALUES (1, 'Profile');
    SAVEPOINT sp2;
    
    INSERT INTO notifications (user_id, message) VALUES (1, 'Welcome');
    
    -- If notification fails, rollback only that part
    ROLLBACK TO sp2;
    -- Users and user_profiles still exist, notification rolled back
    
    INSERT INTO notifications (user_id, message) VALUES (1, 'Welcome updated');
COMMIT;  -- All changes committed

-- Multiple savepoints
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    SAVEPOINT sp_debit;
    
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    SAVEPOINT sp_credit;
    
    UPDATE audit_log SET entries = entries + 1;
    
    -- Rollback to different points based on conditions
    IF audit_failed THEN
        ROLLBACK TO sp_credit;  -- Only reverts audit, keeps debit and credit
    END IF;
COMMIT;

Why it matters: Savepoints enable complex transaction logic with fine-grained error recovery.

Real applications: Complex business logic, bulk operations with checkpoints, conditional commits.

Common mistakes: Nested savepoints adding complexity, not managing savepoint names.

Long-running transactions hold locks longer, increasing deadlock risk and blocking other operations. They consume memory for undo logs and can cause replication lag. Best practice is keeping transactions short—perform heavy computation outside transactions and only lock during necessary database operations.

-- Bad: Long transaction with external processing
BEGIN;
    $order = fetch_order_from_api();  // External API call = seconds of delay
    INSERT INTO orders (data) VALUES ($order);
    $response = call_external_service();  // Another delay
    INSERT INTO response_log VALUES ($response);
COMMIT;

-- Good: External work outside transaction
$order = fetch_order_from_api();  // External work, no lock
$response = call_external_service();

BEGIN;  // Quick transaction
    INSERT INTO orders (data) VALUES ($order);
    INSERT INTO response_log VALUES ($response);
COMMIT;

// Further processing outside transaction
process_order_async($order['id']);

-- Problems with long transactions
-- 1. Locks held too long - blocks other transactions
-- 2. Memory usage - undo logs grow
-- 3. Replication lag - slave falls behind on MySQL replication
-- 4. Recovery time - if server crashes, recovery takes longer

-- Monitor long transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

Why it matters: Long transaction management directly impacts system scalability and reliability.

Real applications: Web applications, API backends, batch processing systems.

Common mistakes: Including external API calls in transactions, not setting transaction timeouts.

Binary log (binlog) records all database changes for recovery and replication. InnoDB redo log helps recover uncommitted changes after crash. Undo log enables transaction rollback. These logs work together: changes are logged, then committed atomically. This ensures both Durability and precise recovery.

-- Transaction logging process
-- 1. Write redo log - prepare for recovery
-- 2. Write change to table
-- 3. Write binlog - for replication
-- 4. Commit - mark as durable

-- Check binary log status
SHOW MASTER STATUS;  -- Shows current binlog file and position
SHOW BINARY LOGS;  -- Lists all binlog files

-- Enable/disable binlog
SET sql_log_bin = 1;  -- Log this transaction to binlog
SET sql_log_bin = 0;  -- Don't log (for testing/bulk operations)

-- Read binlog contents
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- InnoDB redo log configuration
-- Improves crash recovery speed
-- Larger log = faster recovery but more disk usage
SHOW VARIABLES LIKE 'innodb_log%';

-- Undo log (automatic)
-- Stores old values for transaction rollback
-- Also used for MVCC (Multi-Version Concurrency Control)

Why it matters: Understanding transaction logging is essential for disaster recovery and replication setup.

Real applications: Production backups, disaster recovery procedures, master-slave replication.

Common mistakes: Disabling binlog without understanding consequences, not monitoring undo log size.

Optimistic locking assumes conflicts are rare and checks for them at commit time using version numbers or timestamps. Pessimistic locking assumes conflicts are common and locks resources upfront with FOR UPDATE or LOCK IN SHARE MODE. Choice depends on contention level—low contention favors optimistic, high contention favors pessimistic.

-- Optimistic Locking (version-based)
-- Table structure
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2),
    version INT  -- Version counter
);

-- Read product
SELECT id, name, price, version FROM products WHERE id = 1;

-- Application modifies locally
product.price = 99.99;

-- Commit with version check
UPDATE products SET price = 99.99, version = version + 1
WHERE id = 1 AND version = 2;  -- Check old version

IF affected_rows == 0:
    RAISE ConflictError  // Another transaction updated it

-- Pessimistic Locking (lock upfront)
BEGIN;
    SELECT * FROM products WHERE id = 1 FOR UPDATE;
    -- Now this row is locked exclusively
    
    UPDATE products SET price = 99.99 WHERE id = 1;
COMMIT;  -- Lock released

-- Pessimistic with shared lock (readers don't block each other)
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- Optimistic vs Pessimistic Trade-offs
-- Optimistic: Better concurrency, may have more conflicts, needs error handling
-- Pessimistic: Lower concurrency, guaranteed no conflicts, simpler code

Why it matters: Choosing between locking strategies affects both performance and concurrency.

Real applications: Collaborative editing uses optimistic, inventory management uses pessimistic.

Common mistakes: Not implementing retry logic in optimistic locking, overusing pessimistic locking.

Distributed transactions span multiple databases or systems, ensuring atomicity across independent resources. Two-phase commit (2PC) coordinates this with prepare and commit phases. Challenges include complexity, potential for blocking, and network failures. MySQL doesn't natively support cross-server transactions; instead use application logic or XA transactions.

-- XA Transaction (Distributed) - MySQL support
-- Prepare phase
XA START 'xid_value';
    UPDATE accounts SET balance = balance - 100 WHERE server = 'server1';
XA END 'xid_value';
XA PREPARE 'xid_value';

-- Commit phase
XA COMMIT 'xid_value';

-- Or rollback
XA ROLLBACK 'xid_value';

-- Two-Phase Commit Logic (application level)
function transfer_across_dbs(amount):
    try:
        // Phase 1: Prepare
        db1_connection.prepare("DEBIT account");
        db2_connection.prepare("CREDIT account");
        
        // Phase 2: Commit
        db1_connection.commit();
        db2_connection.commit();
    except:
        db1_connection.rollback();
        db2_connection.rollback();
        raise;

-- Challenges with distributed transactions
// 1. Network Partition Risk
// If network fails during 2PC, database stuck in prepared state
// 2. Performance Impact
// Prepare queries lock resources, slowing everything
// 3. Complexity
// More code, more failure scenarios to handle

Why it matters: Microservices architecture requires understanding when and how to use distributed transactions.

Real applications: Cross-database money transfers, multi-system updates, event-driven architectures.

Common mistakes: Using XA for non-critical operations, not handling prepare timeouts.

MVCC maintains multiple versions of rows, allowing readers and writers to work concurrently without blocking. Each transaction sees a consistent snapshot based on when it started. InnoDB uses MVCC with undo logs to store old row versions. This dramatically improves concurrency compared to traditional row locking.

-- MVCC in action
-- Transaction 1 (Reader):
BEGIN;
SELECT * FROM products;  // Sees snapshot from transaction start time

-- Transaction 2 (simultaneously, Writer):
UPDATE products SET price = 99 WHERE id = 1;
COMMIT;

-- Transaction 1 (Reader):
SELECT * FROM products WHERE id = 1;  // Still sees old price = 100
// Because MVCC provides consistent snapshot
COMMIT;

-- MVCC Snapshot Isolation
-- Each transaction has a view of data at specific point in time
-- Determined by:
// 1. Transaction isolation level
// 2. When transaction started
// 3. What other transactions committed before it

-- InnoDB MVCC implementation
// Uses two hidden columns: transaction ID and delete ID
// SELECT * FROM products shows rows visible to current transaction
// Based on version number and transaction state

-- MVCC Benefits
// 1. SELECT queries don't block UPDATE queries
// 2. UPDATE queries don't block SELECT queries
// 3. High concurrency with REPEATABLE READ isolation

Why it matters: MVCC is fundamental to InnoDB's high concurrency model.

Real applications: High-traffic web applications, data warehouses, real-time systems.

Common mistakes: Not understanding that different transactions see different data, assuming all queries see same view.

MySQL default: REPEATABLE READ provides a good balance between consistency and concurrency. It prevents dirty reads and non-repeatable reads but allows phantom reads. This level is stronger than most other databases' defaults (typically READ COMMITTED) and sufficient for most applications without requiring special handling.

-- Check default isolation level
SELECT @@GLOBAL.transaction_isolation;  -- REPEATABLE-READ
SELECT @@SESSION.transaction_isolation;  -- Current session level

-- REPEATABLE READ characteristics
-- 1. Prevents dirty reads - no uncommitted data visible
-- 2. Prevents non-repeatable reads - row values don't change mid-transaction
// 3. Allows phantoms - new rows may appear (gap between range queries)

-- Example of phantom read in REPEATABLE READ
BEGIN;
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  // 5 rows
    
    // Meanwhile, another transaction commits:
    // INSERT INTO orders VALUES (..., 'pending');
    
    SELECT COUNT(*) FROM orders WHERE status = 'pending';  // May see 6 rows!
COMMIT;

-- To prevent phantoms, use SERIALIZABLE (higher overhead)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
    // Locks all matching rows AND gap between them
COMMIT;

-- Performance comparison at different isolation levels
// READ UNCOMMITTED: Fastest, least safe
// READ COMMITTED: Good balance, default in PostgreSQL
// REPEATABLE READ: MySQL default, strong safety
// SERIALIZABLE: Safest, worst performance

Why it matters: Understanding MySQL's default isolation level helps optimize application design without requiring non-standard configurations.

Real applications: Most MySQL applications run with default REPEATABLE READ without issues.

Common mistakes: Changing isolation level unnecessarily, not understanding phantom read implications.

Transaction monitoring involves checking INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS tables. Processlist shows active queries and their states. Look for long-running transactions, lock waits, and deadlocks. Enable the slow query log to capture problematic transactions. Use Performance Schema for detailed metrics.

-- View active transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started;

-- Check transaction command and time
SELECT trx_id, trx_state, trx_started FROM INFORMATION_SCHEMA.INNODB_TRX;

-- Find blocking transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- View current locks
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- Monitor with SHOW PROCESSLIST
SHOW PROCESSLIST;
// Look for "Waiting for table lock", "Lock wait timeout" states

// Kill blocking transaction if necessary
KILL QUERY 123;  // Kill specific query
KILL 123;  // Kill entire connection

-- Check slow transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- Enable Performance Schema for metrics
SHOW VARIABLES LIKE 'performance_schema';

SELECT * FROM performance_schema.events_transactions_summary_by_host_by_event_name;

Why it matters: Effective monitoring helps identify and resolve transaction issues before they impact users.

Real applications: Production support, performance optimization, troubleshooting customer issues.

Common mistakes: Not monitoring transactions in production, not killing blocked queries promptly.

Good transaction design includes: keep transactions short, lock resources in consistent order to prevent deadlocks, implement retry logic for transient failures, use savepoints for complex operations, and perform external work outside transactions. Design for idempotency so retries are safe and fail fast with appropriate error handling.

-- Pattern 1: Short Transaction
// Bad: Long computation inside transaction
BEGIN;
    complex_calculation();  // Takes 5 seconds
    INSERT INTO results VALUES (...);
COMMIT;

// Good: Compute first, then quick transaction
result = complex_calculation();
BEGIN;
    INSERT INTO results VALUES (result);
COMMIT;

-- Pattern 2: Deadlock-safe operation order
// Bad: Inconsistent lock order
connection1: UPDATE table1; UPDATE table2;
connection2: UPDATE table2; UPDATE table1;  // Deadlock!

// Good: Always lock in same order
connection1: UPDATE table2; UPDATE table1;
connection2: UPDATE table2; UPDATE table1;  // No deadlock

-- Pattern 3: Transaction retry with backoff
for attempt in range(3):
    try:
        BEGIN;
            execute_operation();
        COMMIT;
        break;
    except DeadlockError:
        ROLLBACK;
        sleep(random(0, 2^attempt));  // Exponential backoff

-- Pattern 4: Idempotent transactions
// Bad: Non-idempotent (fails on retry)
INSERT INTO accounts VALUES (1, 100);
// If retry, duplicate key error

// Good: Idempotent (safe to retry)
INSERT INTO accounts VALUES (1, 100)
ON DUPLICATE KEY UPDATE balance = 100;
// Retry is safe

Why it matters: Good transaction design patterns prevent data corruption, deadlocks, and improve application reliability under failure conditions.

Real applications: E-commerce checkout, financial transfers, inventory management — all production systems need robust transaction patterns.

Common mistakes: Long-running transactions holding locks, inconsistent lock ordering causing deadlocks, no retry logic for transient errors.