MySQL Interview Questions and Answers: ACID, Isolation Levels, Storage Engines, Indexes, Locks, and More

ACID Transaction Properties

Atomicity : All operations in a transaction succeed or all are rolled back.

Consistency : Integrity constraints remain intact before and after a transaction.

Isolation : Concurrent transactions do not interfere with each other; various isolation levels control phenomena such as dirty reads, non‑repeatable reads, and phantom reads.

Durability : Once committed, changes survive system failures.

Transaction Concurrency and Isolation Levels

The four isolation levels and their phenomena are summarized in the table below.

Isolation Level

Dirty Read

Non‑Repeatable Read

Phantom Read

Read Uncommitted

Yes

Yes

Yes

Read Committed

No

Yes

Yes

Repeatable Read

No

No

Yes

Serializable

No

No

No

MySQL’s default isolation level is repeatable-read.

Common MySQL Storage Engines

InnoDB supports transactions, row‑level locking, foreign keys, and is the default engine since MySQL 5.5.5.

MyISAM is faster for read‑heavy workloads, does not support transactions or foreign keys, and uses table‑level locks.

MEMORY stores data in RAM, uses hash indexes by default, and is suitable for temporary, short‑lived tables.

Engine Selection Example

MyISAM, InnoDB, MERGE, MEMORY, BDB, EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE

Index Types

B+Tree indexes are the default for InnoDB and support range queries, sorting, and prefix scans.

Hash indexes provide O(1) equality lookups but cannot handle range or ORDER BY queries.

SQL Execution Order

The logical execution order is

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

, which differs from the written order.

Temporary Tables

Created with the TEMPORARY keyword, they are visible only to the current session and are dropped automatically when the connection closes.

DROP TEMPORARY TABLE IF EXISTS temp_tb;

CREATE TEMPORARY TABLE tmp_table (
    NAME VARCHAR(10) NOT NULL,
    time DATE NOT NULL
);

Normalization (3NF)

First Normal Form ensures atomic column values; Second Normal Form requires each column to depend on the whole primary key; Third Normal Form removes transitive dependencies.

Read‑Write Splitting and Master‑Slave Replication

Writes go to the master, reads are distributed among slaves using round‑robin or a proxy such as MySQL‑Proxy or HAProxy. Replication can be asynchronous, semi‑synchronous, or synchronous.

Using EXPLAIN for Query Optimization

EXPLAIN shows the execution plan, including type (e.g., ref, range, ALL), possible_keys, key, rows, and Extra information such as Using filesort or Using temporary.

Slow Query Log

Enable the slow query log, set long_query_time, and specify slow_query_log_file to capture inefficient statements.

Join Types

Inner join returns matching rows; left/right outer joins preserve rows from the left/right table; full outer join returns all rows; cross join produces the Cartesian product.

SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city;

Lock Types and Deadlock Handling

MySQL provides table‑level, page‑level, and row‑level locks. InnoDB row locks can deadlock; avoid by acquiring locks in a consistent order or setting innodb_lock_wait_timeout.

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

SET innodb_lock_wait_timeout=1000;

Char vs Varchar

CHAR(10)

always occupies 10 bytes (padded with spaces), while VARCHAR(10) stores only the actual length plus a length byte, making it more space‑efficient but slightly slower.

High Concurrency Solutions

Techniques include sharding (horizontal partitioning), database clustering, read‑write splitting, and adding secondary caches such as Memcached.

Transaction Recovery: Undo and Redo Logs

Undo Log records before‑image data to enable rollback and supports MVCC.

Redo Log records after‑image changes; persisting the redo log before commit ensures durability without writing data pages immediately.

Contact Information

Author: Peng Lei – http://www.ymq.io