Mysql

MySQL

Use this skill to make safe, measurable MySQL/InnoDB changes.

Workflow

1. Define workload and constraints (read/write mix, latency target, data volume, MySQL version, hosting platform). 2. Read only the relevant reference files linked in each section below. 3. Propose the smallest change that can solve the problem, including trade-offs. 4. Validate with evidence (EXPLAIN, EXPLAIN ANALYZE, lock/connection metrics, and production-safe rollout steps). 5. For production changes, include rollback and post-deploy verification.

Schema Design

• Prefer narrow, monotonic PKs (BIGINT UNSIGNED AUTO_INCREMENT) for write-heavy OLTP tables.
• Avoid random UUID values as clustered PKs; if external IDs are required, keep UUID in a secondary unique column.
• Always utf8mb4 / utf8mb4_0900_ai_ci. Prefer NOT NULL, DATETIME over TIMESTAMP.
• Lookup tables over ENUM. Normalize to 3NF; denormalize only for measured hot paths.

References:

• primary-keys
• data-types
• character-sets
• json-column-patterns

Indexing

• Composite order: equality first, then range/sort (leftmost prefix rule).
• Range predicates stop index usage for subsequent columns.
• Secondary indexes include PK implicitly. Prefix indexes for long strings.
• Audit via performance_schema — drop indexes with count_read = 0.

References:

• composite-indexes
• covering-indexes
• fulltext-indexes
• index-maintenance

Partitioning

• Partition time-series (>50M rows) or large tables (>100M rows). Plan early — retrofit = full rebuild.
• Include partition column in every unique/PK. Always add a MAXVALUE catch-all.

References:

• partitioning

Query Optimization

• Check EXPLAIN — red flags: type: ALL, Using filesort, Using temporary.
• Cursor pagination, not OFFSET. Avoid functions on indexed columns in WHERE.
• Batch inserts (500–5000 rows). UNION ALL over UNION when dedup unnecessary.

References:

• explain-analysis
• query-optimization-pitfalls
• n-plus-one

Transactions & Locking

• Default: REPEATABLE READ (gap locks). Use READ COMMITTED for high contention.
• Consistent row access order prevents deadlocks. Retry error 1213 with backoff.
• Do I/O outside transactions. Use SELECT ... FOR UPDATE sparingly.

References:

• isolation-levels
• deadlocks
• row-locking-gotchas

Operations

• Use online DDL (ALGORITHM=INPLACE) when possible; test on replicas first.
• Tune connection pooling — avoid max_connections exhaustion under load.
• Monitor replication lag; avoid stale reads from replicas during writes.

References:

• online-ddl
• connection-management
• replication-lag

Guardrails

• Prefer measured evidence over blanket rules of thumb.
• Note MySQL-version-specific behavior when giving advice.
• Ask for explicit human approval before destructive data operations (drops/deletes/truncates).

Character Sets and Collations

Always Use utf8mb4

MySQL's utf8 = utf8mb3 (3-byte only, no emoji/many CJK). Always utf8mb4.

CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Collation Quick Reference

_0900_ = Unicode 9.0 (preferred over older _unicode_ variants).

Collation Behavior

Collations affect string comparisons, sorting (ORDER BY), and pattern matching (LIKE):

• Case-insensitive (`_ci`): 'A' = 'a' evaluates to true, LIKE 'a%' matches 'Apple'
• Case-sensitive (`_cs`): 'A' = 'a' evaluates to false, LIKE 'a%' matches only lowercase
• Accent-insensitive (`_ai`): 'e' = 'é' evaluates to true
• Accent-sensitive (`_as`): 'e' = 'é' evaluates to false
• Binary (`_bin`): strict byte-by-byte comparison (most restrictive)

You can override collation per query:

SELECT * FROM users
WHERE name COLLATE utf8mb4_0900_as_cs = 'José';

Migrating from utf8/utf8mb3

-- Find columns still using utf8
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'mydb' AND character_set_name = 'utf8';
-- Convert
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Warning: index key length limits depend on InnoDB row format:

• DYNAMIC/COMPRESSED: 3072 bytes max (≈768 chars with utf8mb4)
• REDUNDANT/COMPACT: 767 bytes max (≈191 chars with utf8mb4)

VARCHAR(255) with utf8mb4 = up to 1020 bytes (4×255). That's safe for DYNAMIC/COMPRESSED but exceeds REDUNDANT/COMPACT limits.

Connection

Ensure client uses utf8mb4: SET NAMES utf8mb4; (most modern drivers default to this).

SET NAMES utf8mb4 sets three session variables:

• character_set_client (encoding for statements sent to server)
• character_set_connection (encoding for statement processing)
• character_set_results (encoding for results sent to client)

It also sets collation_connection to the default collation for utf8mb4.

Composite Indexes

Leftmost Prefix Rule

Index (a, b, c) is usable for:

• WHERE a (uses column a)
• WHERE a AND b (uses columns a, b)
• WHERE a AND b AND c (uses all columns)
• WHERE a AND c (uses only column a; c can't filter without b)

NOT usable for WHERE b alone or WHERE b AND c (the search must start from the leftmost column).

Column Order: Equality First, Then Range/Sort

-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_tenant_status_created ON orders (tenant_id, status, created_at);

Critical: Range predicates (>, <, BETWEEN, LIKE 'prefix%', and sometimes large IN (...)) stop index usage for filtering subsequent columns. However, columns after a range predicate can still be useful for:

• Covering index reads (avoid table lookups)
• ORDER BY/GROUP BY in some cases, when the ordering/grouping matches the usable index prefix

Sort Order Must Match Index

-- Index: (status, created_at)
ORDER BY status ASC, created_at ASC   -- ✓ matches (optimal)
ORDER BY status DESC, created_at DESC -- ✓ full reverse OK (reverse scan)
ORDER BY status ASC, created_at DESC  -- ⚠️ mixed directions (may use filesort)

-- MySQL 8.0+: descending index components
CREATE INDEX idx_orders_status_created ON orders (status ASC, created_at DESC);

Composite vs Multiple Single-Column Indexes

MySQL can merge single-column indexes (index_merge union/intersection) but a composite index is typically faster. Index merge is useful when queries filter on different column combinations that don't share a common prefix, but it adds overhead and may not scale well under load.

Selectivity Considerations

Within equality columns, place higher-cardinality (more selective) columns first when possible. However, query patterns and frequency usually matter more than pure selectivity.

GROUP BY and Composite Indexes

GROUP BY can benefit from composite indexes when the GROUP BY columns match the index prefix. MySQL may use the index to avoid sorting.

Design for Multiple Queries

-- One index covers: WHERE user_id=?, WHERE user_id=? AND status=?,
--   and WHERE user_id=? AND status=? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

InnoDB Secondary Index Behavior

InnoDB secondary indexes implicitly store the primary key value with each index entry. This means a secondary index can sometimes "cover" primary key lookups without adding the PK columns explicitly.

Connection Management

Every MySQL connection costs memory (~1–10 MB depending on buffers). Unbounded connections cause OOM or Too many connections errors.

Sizing max_connections

Default is 151. Don't blindly raise it — more connections = more memory + more contention.

SHOW VARIABLES LIKE 'max_connections';         -- current limit
SHOW STATUS LIKE 'Max_used_connections';        -- high-water mark
SHOW STATUS LIKE 'Threads_connected';           -- current count

Pool Sizing Formula

A good starting point for OLTP: *pool size = (CPU cores N)** where N is typically 2-10. This is a baseline — tune based on:

• Query characteristics (I/O-bound queries may benefit from more connections)
• Actual connection usage patterns (monitor Threads_connected vs Max_used_connections)
• Application concurrency requirements

More connections beyond CPU-bound optimal add context-switch overhead without improving throughput.

Timeout Tuning

Idle Connection Timeouts

-- Kill idle connections after 5 minutes (default is 28800 seconds / 8 hours — way too long)
SET GLOBAL wait_timeout = 300;         -- Non-interactive connections (apps)
SET GLOBAL interactive_timeout = 300;  -- Interactive connections (CLI)

Note: These are server-side timeouts. The server closes idle connections after this period. Client-side connection timeouts (e.g., connectTimeout in JDBC) are separate and control connection establishment.

Active Query Timeouts

-- Increase for bulk operations or large result sets (default: 30 seconds)
SET GLOBAL net_read_timeout = 60;      -- Time server waits for data from client
SET GLOBAL net_write_timeout = 60;     -- Time server waits to send data to client

These apply to active data transmission, not idle connections. Increase if you see errors like Lost connection to MySQL server during query during bulk inserts or large SELECTs.

Thread Handling

MySQL uses a one-thread-per-connection model by default: each connection gets its own OS thread. This means max_connections directly impacts thread count and memory usage.

MySQL also caches threads for reuse. If connections fluctuate frequently, increase thread_cache_size to reduce thread creation overhead.

Common Pitfalls

• ORM default pools too large: Rails default is 5 per process — 20 Puma workers = 100 connections from one app server. Multiply by app server count.
• No pool at all: PHP/CGI models open a new connection per request. Use persistent connections or ProxySQL.
• Connection storms on deploy: All app servers reconnect simultaneously when restarted, potentially exhausting max_connections. Mitigations: stagger deployments, use connection pool warm-up (gradually open connections), or use a proxy layer.
• Idle transactions: Connections with open transactions (BEGIN without COMMIT/ROLLBACK) are not closed by wait_timeout and hold locks. This causes deadlocks and connection leaks. Always commit or rollback promptly, and use application-level transaction timeouts.

Prepared Statements

Use prepared statements with connection pooling for performance and safety:

• Performance: reduces repeated parsing for parameterized queries
• Security: helps prevent SQL injection

Note: prepared statements are typically connection-scoped; some pools/drivers provide statement caching.

When to Use a Proxy

Use ProxySQL or PlanetScale connection pooling when: multiple app services share a DB, you need query routing (read/write split), or total connection demand exceeds safe max_connections.

Vitess / PlanetScale Note

If running on PlanetScale (or Vitess), connection pooling is handled at the Vitess vtgate layer. This means your app can open many connections to vtgate without each one mapping 1:1 to a MySQL backend connection. Backend connection issues are minimized under this architecture.

Covering Indexes

A covering index contains all columns a query needs — InnoDB satisfies it from the index alone (Using index in EXPLAIN Extra).

-- Query: SELECT user_id, status, total FROM orders WHERE user_id = 42
-- Covering index (filter columns first, then included columns):
CREATE INDEX idx_orders_cover ON orders (user_id, status, total);

InnoDB Implicit Covering

Because InnoDB secondary indexes store the primary key value with each index entry, INDEX(status) already covers SELECT id FROM t WHERE status = ? (where id is the PK).

ICP vs Covering Index

• ICP (`Using index condition`): engine filters at the index level before accessing table rows, but still requires table lookups.
• Covering index (`Using index`): query is satisfied entirely from the index, with no table lookups.

EXPLAIN Signals

Look for Using index in the Extra column:

EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Extra: Using index ✓

If you see Using index condition instead, the index is helping but not covering — you may need to add selected columns to the index.

When to Use

• High-frequency reads selecting few columns from wide tables.
• Not worth it for: wide result sets (TEXT/BLOB), write-heavy tables, low-frequency queries.

Tradeoffs

• Write amplification: every INSERT/UPDATE/DELETE must update all relevant indexes.
• Index size: wide indexes consume more disk and buffer pool memory.
• Maintenance: larger indexes take longer to rebuild during ALTER TABLE.

Guidelines

• Add columns to existing indexes rather than creating new ones.
• Order: filter columns first, then additional covered columns.
• Verify Using index appears in EXPLAIN after adding the index.
• Pitfall: SELECT * defeats covering indexes — select only the columns you need.

Data Types

Choose the smallest correct type — more rows per page, better cache, faster queries.

Numeric Sizes

Use BIGINT UNSIGNED for PKs — INT exhausts at ~4.3B rows. Use DECIMAL(19,4) for money, never FLOAT.

Strings

• VARCHAR(N) over TEXT when bounded — can be indexed directly.
• `N` matters: VARCHAR(255) vs VARCHAR(50) affects memory allocation for temp tables and sorts.

TEXT/BLOB Indexing

• You generally can't index TEXT/BLOB fully; use prefix indexes: INDEX(text_col(255)).
• Prefix length limits depend on InnoDB row format:
• DYNAMIC/COMPRESSED: 3072 bytes max (≈768 chars with utf8mb4)
• REDUNDANT/COMPACT: 767 bytes max (≈191 chars with utf8mb4)
• For keyword search, consider FULLTEXT indexes instead of large prefix indexes.

Date/Time

• TIMESTAMP: 4 bytes, auto-converts timezone, but 2038 limit. Use DATETIME for dates beyond 2038.

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

JSON

Use for truly dynamic data only. Index JSON values via generated columns:

ALTER TABLE products
  ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.color') STORED,
  ADD INDEX idx_color (color);

Prefer simpler types like integers and strings over JSON.

Generated Columns

Use generated columns for computed values, JSON extraction, or functional indexing:

-- VIRTUAL (default): computed on read, no storage
ALTER TABLE orders
  ADD COLUMN total_cents INT GENERATED ALWAYS AS (price_cents * quantity) VIRTUAL;

-- STORED: computed on write, can be indexed
ALTER TABLE products
  ADD COLUMN name_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED,
  ADD INDEX idx_name_lower (name_lower);

Choose VIRTUAL for simple expressions when space matters. Choose STORED when indexing is required or the expression is expensive.

ENUM/SET

Prefer lookup tables — ENUM/SET changes require ALTER TABLE, which can be slow on large tables.

Deadlocks

InnoDB auto-detects deadlocks and rolls back one transaction (the "victim").

Common Causes

1. Opposite row ordering — Transactions accessing the same rows in different order can deadlock. Fix: always access rows in a consistent order (typically by primary key or a common index) so locks are acquired in the same sequence. 2. Next-key lock conflicts (REPEATABLE READ) — InnoDB uses next-key locks (row + gap) to prevent phantoms. Fix: use READ COMMITTED (reduces gap locking) or narrow lock scope. 3. Missing index on WHERE column — UPDATE/DELETE without an index may require a full table scan, locking many rows unnecessarily and increasing deadlock risk. 4. AUTO_INCREMENT lock contention — Concurrent INSERT patterns can deadlock while contending on the auto-inc lock. Fix: use innodb_autoinc_lock_mode=2 (interleaved) for better concurrency when safe for your workload, or batch inserts.

Note: SERIALIZABLE also uses gap/next-key locks. READ COMMITTED reduces some gap-lock deadlocks but doesn't eliminate deadlocks from opposite ordering or missing indexes.

Diagnosing

-- Last deadlock details
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" section

-- Current lock waits (MySQL 8.0+)
SELECT object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks WHERE lock_status = 'WAITING';

-- Lock wait relationships (MySQL 8.0+)
SELECT
  w.requesting_thread_id,
  w.requested_lock_id,
  w.blocking_thread_id,
  w.blocking_lock_id,
  l.lock_type,
  l.lock_mode,
  l.lock_data
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l ON w.requested_lock_id = l.lock_id;

Prevention

• Keep transactions short. Do I/O outside transactions.
• Ensure WHERE columns in UPDATE/DELETE are indexed.
• Use SELECT ... FOR UPDATE sparingly. Batch large updates with LIMIT.
• Access rows in a consistent order (by PK or index) across all transactions.

Retry Pattern (Error 1213)

In applications, retries are a common workaround for occasional deadlocks.

Important: ensure the operation is idempotent (or can be safely retried) before adding automatic retries, especially if there are side effects outside the database.

def execute_with_retry(db, fn, max_retries=3):
    for attempt in range(max_retries):
        try:
            with db.begin():
                return fn()
        except OperationalError as e:
            if e.args[0] == 1213 and attempt < max_retries - 1:
                time.sleep(0.05 * (2 ** attempt))
                continue
            raise

Common Misconceptions

• "Deadlocks are bugs" — deadlocks are a normal part of concurrent systems. The goal is to minimize frequency, not eliminate them entirely.
• "READ COMMITTED eliminates deadlocks" — it reduces gap/next-key lock deadlocks, but deadlocks still happen from opposite ordering, missing indexes, and lock contention.
• "All deadlocks are from gap locks" — many are caused by opposite row ordering even without gap locks.
• "Victim selection is random" — InnoDB generally chooses the transaction with lower rollback cost (fewer rows changed).

EXPLAIN Analysis

EXPLAIN SELECT ...;                    -- estimated plan
EXPLAIN FORMAT=JSON SELECT ...;        -- detailed with cost estimates
EXPLAIN FORMAT=TREE SELECT ...;        -- tree format (8.0+)
EXPLAIN ANALYZE SELECT ...;            -- actual execution (8.0.18+, runs the query, uses TREE format)

Access Types (Best → Worst)

system → const → eq_ref → ref → range → index (full index scan) → ALL (full table scan)

Target ref or better. ALL on >1000 rows almost always needs an index.

Key Extra Flags

key_len — How Much of Composite Index Is Used

Byte sizes: TINYINT=1, INT=4, BIGINT=8, DATE=3, DATETIME=5, VARCHAR(N) utf8mb4: N×4+1 (or +2 when N×4>255). Add 1 byte per nullable column.

-- Index: (status TINYINT, created_at DATETIME)
-- key_len=2 → only status (1+1 null). key_len=8 → both columns used.

rows vs filtered

• rows: estimated rows examined after index access (before additional WHERE filtering)
• filtered: percent of examined rows expected to pass the full WHERE conditions
• Rough estimate of rows that satisfy the query: rows × filtered / 100
• Low filtered often means additional (non-indexed) predicates are filtering out lots of rows

Join Order

Row order in EXPLAIN output reflects execution order: the first row is typically the first table read, and subsequent rows are joined in order. Use this to spot suboptimal join ordering (e.g., starting with a large table when a selective table could drive the join).

EXPLAIN ANALYZE

Availability: MySQL 8.0.18+

Important: EXPLAIN ANALYZE actually executes the query (it does not return the result rows). It uses FORMAT=TREE automatically.

Metrics (TREE output):

• actual time: milliseconds (startup → end)
• rows: actual rows produced by that iterator
• loops: number of times the iterator ran

Compare estimated vs actual to find optimizer misestimates. Large discrepancies often improve after refreshing statistics:

ANALYZE TABLE your_table;

Limitations / pitfalls:

• Adds instrumentation overhead (measurements are not perfectly "free")
• Cost units (arbitrary) and time (ms) are different; don't compare them directly
• Results reflect real execution, including buffer pool/cache effects (warm cache can hide I/O problems)

Fulltext Indexes

Fulltext indexes are useful for keyword text search in MySQL. For advanced ranking, fuzzy matching, or complex document search, prefer a dedicated search engine.

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

-- Natural language (default, sorted by relevance)
SELECT *, MATCH(title, body) AGAINST('database performance') AS score
FROM articles WHERE MATCH(title, body) AGAINST('database performance');

-- Boolean mode: + required, - excluded, * suffix wildcard, "exact phrase"
WHERE MATCH(title, body) AGAINST('+mysql -postgres +optim*' IN BOOLEAN MODE);

Key Gotchas

• Min word length: default 3 chars (innodb_ft_min_token_size). Shorter words are ignored. Changing this requires rebuilding the FULLTEXT index (drop/recreate) to take effect.
• Stopwords: common words excluded. Control stopwords with innodb_ft_enable_stopword and customize via innodb_ft_user_stopword_table / innodb_ft_server_stopword_table (set before creating the index, then rebuild to apply changes).
• No partial matching: unlike LIKE '%term%', requires whole tokens (except * in boolean mode).
• MATCH() columns must correspond to an index definition: MATCH(title, body) needs a FULLTEXT index that covers the same column set (e.g. (title, body)).
• Boolean mode without required terms (no leading +) can match a very large portion of the index and be slow.
• Fulltext adds write overhead — consider Elasticsearch/Meilisearch for complex search needs.

Index Maintenance

Find Unused Indexes

-- Requires performance_schema enabled (default in MySQL 5.7+)
-- "Unused" here means no reads/writes since last restart.
SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL AND index_name != 'PRIMARY'
  AND COUNT_READ = 0 AND COUNT_WRITE = 0
ORDER BY COUNT_WRITE DESC;

Sometimes you'll also see indexes with writes but no reads (overhead without query benefit). Review these carefully: some are required for constraints (UNIQUE/PK) even if not used in query plans.

SELECT object_schema, object_name, index_name, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND index_name IS NOT NULL AND index_name != 'PRIMARY'
  AND COUNT_READ = 0 AND COUNT_WRITE > 0
ORDER BY COUNT_WRITE DESC;

Counters reset on restart — ensure 1+ full business cycle of uptime before dropping.

Find Redundant Indexes

Index on (a) is redundant if (a, b) exists (leftmost prefix covers it). Pairs sharing only the first column (e.g. (a,b) vs (a,c)) need manual review — neither is redundant.

-- Prefer sys schema view (MySQL 5.7.7+)
SELECT table_schema, table_name,
  redundant_index_name, redundant_index_columns,
  dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'mydb';

Check Index Sizes

SELECT database_name, table_name, index_name,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND database_name = 'mydb'
ORDER BY stat_value DESC;
-- stat_value is in pages; multiply by innodb_page_size for bytes

Index Write Overhead

Each index must be updated on INSERT, UPDATE, and DELETE operations. More indexes = slower writes.

• INSERT: each secondary index adds a write
• UPDATE: changing indexed columns updates all affected indexes
• DELETE: removes entries from all indexes

InnoDB can defer some secondary index updates via the change buffer, but excessive indexing still reduces write throughput.

Update Statistics (ANALYZE TABLE)

The optimizer relies on index cardinality and distribution statistics. After large data changes, refresh statistics:

ANALYZE TABLE orders;

This updates statistics (does not rebuild the table).

Rebuild / Reclaim Space (OPTIMIZE TABLE)

OPTIMIZE TABLE can reclaim space and rebuild indexes:

OPTIMIZE TABLE orders;

For InnoDB this effectively rebuilds the table and indexes and can be slow on large tables.

Invisible Indexes (MySQL 8.0+)

Test removing an index without dropping it:

ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

Invisible indexes are still maintained on writes (overhead remains), but the optimizer won't consider them.

Index Maintenance Tools

Online DDL (Built-in)

Most add/drop index operations are online-ish but still take brief metadata locks:

ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change / gh-ost

For very large tables or high-write workloads, online schema change tools can reduce blocking by using a shadow table and a controlled cutover (tradeoffs: operational complexity, privileges, triggers/binlog requirements).

Guidelines

• 1–5 indexes per table is normal. 6+: audit for redundancy.
• Combine performance_schema data with EXPLAIN of frequent queries monthly.

Isolation Levels (InnoDB Best Practices)

Default to REPEATABLE READ. It is the InnoDB default, most tested, and prevents phantom reads. Only change per-session with a measured reason.

SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- per-session only

Autocommit Interaction

• Default: autocommit=1 (each statement is its own transaction).
• With autocommit=0, transactions span multiple statements until COMMIT/ROLLBACK.
• Isolation level applies per transaction. SERIALIZABLE behavior differs based on autocommit setting (see SERIALIZABLE section).

Locking vs Non-Locking Reads

• Non-locking reads: plain SELECT statements use consistent reads (MVCC snapshots). They don't acquire locks and don't block writers.
• Locking reads: SELECT ... FOR UPDATE (exclusive) or SELECT ... FOR SHARE (shared) acquire locks and can block concurrent modifications.
• UPDATE and DELETE statements are implicitly locking reads.

REPEATABLE READ (Default — Prefer This)

• Consistent reads: snapshot established at first read; all plain SELECTs within the transaction read from that same snapshot (MVCC). Plain SELECTs are non-locking and don't block writers.
• Locking reads/writes use next-key locks (row + gap) — prevents phantoms. Exception: a unique index with a unique search condition locks only the index record, not the gap.
• Use for: OLTP, check-then-insert, financial logic, reports needing consistent snapshots.
• Avoid mixing locking statements (SELECT ... FOR UPDATE, UPDATE, DELETE) with non-locking SELECT statements in the same transaction — they can observe different states (current vs snapshot) and lead to surprises.

READ COMMITTED (Per-Session Only, When Needed)

• Fresh snapshot per SELECT; record locks only (gap locks disabled for searches/index scans, but still used for foreign-key and duplicate-key checks) — more concurrency, but phantoms possible.
• Switch only when: gap-lock deadlocks confirmed via SHOW ENGINE INNODB STATUS, bulk imports with contention, or high-write concurrency on overlapping ranges.
• Never switch globally. Check-then-insert patterns break — use INSERT ... ON DUPLICATE KEY or FOR UPDATE instead.

SERIALIZABLE — Avoid

Converts all plain SELECTs to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, SELECTs are consistent (non-locking) reads. SERIALIZABLE can cause massive contention when autocommit is disabled. Prefer explicit SELECT ... FOR UPDATE at REPEATABLE READ instead — same safety, far less lock scope.

READ UNCOMMITTED — Never Use

Dirty reads with no valid production use case.

Decision Guide

JSON Column Patterns

MySQL 5.7+ supports native JSON columns. Useful, but with important caveats.

When JSON Is Appropriate

• Truly schema-less data (user preferences, metadata bags, webhook payloads).
• Rarely filtered/joined — if you query a JSON path frequently, extract it to a real column.

Indexing JSON: Use Generated Columns

You cannot index a JSON column directly. Create a virtual generated column and index that:

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(50) GENERATED ALWAYS AS (data->>'$.type') VIRTUAL,
  ADD INDEX idx_event_type (event_type);

Extraction Operators

Always use ->> (unquote) in WHERE clauses, otherwise you compare against "foo" (with quotes).

Tip: the generated column example above can be written more concisely as:

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(50) GENERATED ALWAYS AS (data->>'$.type') VIRTUAL,
  ADD INDEX idx_event_type (event_type);

Multi-Valued Indexes (MySQL 8.0.17+)

If you store arrays in JSON (e.g., tags: ["electronics","sale"]), MySQL 8.0.17+ supports multi-valued indexes to index array elements:

ALTER TABLE products
  ADD INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)));

This can accelerate membership queries such as:

SELECT * FROM products WHERE 'electronics' MEMBER OF (tags);

Collation and Type Casting Pitfalls

• JSON type comparisons: JSON_EXTRACT returns JSON type. Comparing directly to strings can be wrong for numbers/dates.

-- WRONG: lexicographic string comparison
WHERE data->>'$.price' <= '1200'

-- CORRECT: cast to numeric
WHERE CAST(data->>'$.price' AS UNSIGNED) <= 1200

• Collation: values extracted with ->> behave like strings and use a collation. Use COLLATE when you need a specific comparison behavior.

WHERE data->>'$.status' COLLATE utf8mb4_0900_as_cs = 'Active'

Common Pitfalls

• Heavy update cost: JSON_SET/JSON_REPLACE can touch large portions of a JSON document and generate significant redo/undo work on large blobs.
• No partial indexes: You can only index extracted scalar paths via generated columns.
• Large documents hurt: JSON stored inline in the row. Documents >8 KB spill to overflow pages, hurting read performance.
• Type mismatches: JSON_EXTRACT returns a JSON type. Comparing with = 'foo' may not match — use ->> or JSON_UNQUOTE.
• VIRTUAL vs STORED generated columns: VIRTUAL columns compute on read (less storage, more CPU). STORED columns materialize on write (more storage, faster reads if selected often). Both can be indexed; for indexed paths, the index stores the computed value either way.

N+1 Query Detection

What Is N+1?

The N+1 pattern occurs when you fetch N parent records, then execute N additional queries (one per parent) to fetch related data.

Example: 1 query for users + N queries for posts.

ORM Fixes (Quick Reference)

• SQLAlchemy 1.x: session.query(User).options(joinedload(User.posts))
• SQLAlchemy 2.0: select(User).options(joinedload(User.posts))
• Django: select_related('fk_field') for FK/O2O, prefetch_related('m2m_field') for M2M/reverse FK
• ActiveRecord: User.includes(:orders)
• Prisma: findMany({ include: { orders: true } })
• Drizzle: use .leftJoin() instead of loop queries

// Drizzle example: avoid N+1 with a join
const rows = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

Detecting in MySQL Production

-- High-frequency simple queries often indicate N+1
-- Requires performance_schema enabled (default in MySQL 5.7+)
SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC LIMIT 20;

Also check the slow query log sorted by count for frequently repeated simple SELECTs.

Batch Consolidation

Replace sequential queries with WHERE id IN (...).

Practical limits:

• Total statement size is capped by max_allowed_packet (often 4MB by default).
• Very large IN lists increase parsing/planning overhead and can hurt performance.

Strategies:

• Up to ~1000–5000 ids: IN (...) is usually fine.
• Larger: chunk the list (e.g. batches of 500–1000) or use a temporary table and join.

-- Temporary table approach for large batches
CREATE TEMPORARY TABLE temp_user_ids (id BIGINT PRIMARY KEY);
INSERT INTO temp_user_ids VALUES (1), (2), (3);

SELECT p.*
FROM posts p
JOIN temp_user_ids t ON p.user_id = t.id;

Joins vs Separate Queries

• Prefer JOINs when you need related data for most/all parent rows and the result set stays reasonable.
• Prefer separate queries (batched) when JOINs would explode rows (one-to-many) or over-fetch too much data.

Eager Loading Caveats

• Over-fetching: eager loading pulls all related rows unless you filter it.
• Memory: loading large collections can blow up memory.
• Row multiplication: JOIN-based eager loading can create huge result sets; in some ORMs, a "select-in" strategy is safer.

Prepared Statements

Prepared statements reduce repeated parse/optimize overhead for repeated parameterized queries, but they do not eliminate N+1: you still execute N queries. Use batching/eager loading to reduce query count.

Pagination Pitfalls

N+1 often reappears per page. Ensure eager loading or batching is applied to the paginated query, not inside the per-row loop.

Online DDL

Not all ALTER TABLE is equal — some block writes for the entire duration.

Algorithm Spectrum

MySQL picks the fastest available. Specify explicitly to fail-safe:

ALTER TABLE orders ADD COLUMN note VARCHAR(255) DEFAULT NULL, ALGORITHM=INSTANT;
-- Fails loudly if INSTANT isn't possible, rather than silently falling back to COPY.

What Supports INSTANT (MySQL 8.0+)

• Adding a column (at any position as of 8.0.29; only at end before 8.0.29)
• Dropping a column (8.0.29+)
• Renaming a column (8.0.28+)

Not INSTANT: adding indexes (uses INPLACE), dropping indexes (uses INPLACE; typically metadata-only), changing column type, extending VARCHAR (uses INPLACE), adding columns when INSTANT isn't supported for the table/operation.

Lock Levels

LOCK=NONE (concurrent DML), LOCK=SHARED (reads only), LOCK=EXCLUSIVE (full block), LOCK=DEFAULT (server chooses maximum concurrency; default).

Always request LOCK=NONE (and an explicit ALGORITHM) to surface conflicts early instead of silently falling back to a more blocking method.

Large Tables (millions+ rows)

Even INPLACE operations typically hold brief metadata locks at start/end. The commit phase requires an exclusive metadata lock and will wait for concurrent transactions to finish; long-running transactions can block DDL from completing.

On huge tables, consider external tools:

• pt-online-schema-change: creates shadow table, syncs via triggers.
• gh-ost: triggerless, uses binlog stream. Preferred for high-write tables.

Replication Considerations

• DDL replicates to replicas and executes there, potentially causing lag (especially COPY-like rebuilds).
• INSTANT operations minimize replication impact because they complete quickly.
• INPLACE operations can still cause lag and metadata lock waits on replicas during apply.

PlanetScale Users

On PlanetScale, use deploy requests instead of manual DDL tools. Vitess handles non-blocking migrations automatically. Use this whenever possible because it offers much safer schema migrations.

Key Rule

Never run ALTER TABLE on production without checking the algorithm. A surprise COPY on a 100M-row table can lock writes for hours.

Partitioning

All columns used in the partitioning expression must be part of every UNIQUE/PRIMARY KEY.

Partition Pruning

The optimizer can eliminate partitions that cannot contain matching rows based on the WHERE clause ("partition pruning"). Partitioning helps most when queries frequently filter by the partition key/expression:

• Equality: WHERE partition_key = ? (HASH/KEY)
• Ranges: WHERE partition_key BETWEEN ? AND ? (RANGE)
• IN lists: WHERE partition_key IN (...) (LIST)

Types

-- RANGE COLUMNS (direct date comparisons; avoids function wrapper)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2025_q1 VALUES LESS THAN ('2025-04-01'),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- RANGE with function (use when you must partition by an expression)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2025_q1 VALUES LESS THAN (TO_DAYS('2025-04-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- LIST (discrete categories — unlisted values cause errors, ensure full coverage)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_americas VALUES IN ('us', 'ca', 'br'),
  PARTITION p_europe  VALUES IN ('uk', 'de', 'fr')
);
-- HASH/KEY (even distribution, equality pruning only)
PARTITION BY HASH (user_id) PARTITIONS 8;

Foreign Key Restrictions (InnoDB)

Partitioned InnoDB tables do not support foreign keys:

• A partitioned table cannot define foreign key constraints to other tables.
• Other tables cannot reference a partitioned table with a foreign key.

If you need foreign keys, partitioning may not be an option.

When Partitioning Helps vs Hurts

Helps:

• Very large tables (millions+ rows) with time-ordered access patterns
• Data retention workflows (drop old partitions vs DELETE)
• Queries that filter by the partition key/expression (enables pruning)
• Maintenance on subsets of data (operate on partitions vs whole table)

Hurts:

• Small tables (overhead without benefit)
• Queries that don't filter by the partition key (no pruning)
• Workloads that require foreign keys
• Complex UNIQUE key requirements (partition key columns must be included everywhere)

Management Operations

-- Add: split catch-all MAXVALUE partition
ALTER TABLE events REORGANIZE PARTITION p_future INTO (
  PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Drop aged-out data (orders of magnitude faster than DELETE)
ALTER TABLE events DROP PARTITION p2025_q1;
-- Merge partitions
ALTER TABLE events REORGANIZE PARTITION p2025_01, p2025_02, p2025_03 INTO (
  PARTITION p2025_q1 VALUES LESS THAN (TO_DAYS('2025-04-01'))
);
-- Archive via exchange (LIKE creates non-partitioned copy; both must match structure)
CREATE TABLE events_archive LIKE events;
ALTER TABLE events_archive REMOVE PARTITIONING;
ALTER TABLE events EXCHANGE PARTITION p2025_q1 WITH TABLE events_archive;

Notes:

• REORGANIZE PARTITION rebuilds the affected partition(s).
• EXCHANGE PARTITION requires an exact structure match (including indexes) and the target table must not be partitioned.
• DROP PARTITION is DDL (fast) vs DELETE (DML; slow on large datasets).

Always ask for human approval before dropping, deleting, or archiving data.

Primary Keys

InnoDB stores rows in primary key order (clustered index). This means:

• Sequential keys = optimal inserts: new rows append, minimizing page splits and fragmentation.
• Random keys = fragmentation: random inserts cause page splits to maintain PK order, wasting space and slowing inserts.
• Secondary index lookups: secondary indexes store the PK value and use it to fetch the full row from the clustered index.

INT vs BIGINT for Primary Keys

• INT UNSIGNED: 4 bytes, max ~4.3B rows.
• BIGINT UNSIGNED: 8 bytes, max ~18.4 quintillion rows.

Guideline: default to BIGINT UNSIGNED unless you're certain the table will never approach the INT limit. The extra 4 bytes is usually cheaper than the risk of exhausting INT.

Avoid Random UUID as Clustered PK

• UUID PK stored as BINARY(16): 16 bytes (vs 8 for BIGINT). Random inserts cause page splits, and every secondary index entry carries the PK.
• UUID stored as CHAR(36)/VARCHAR(36): 36 bytes (+ overhead) and is generally worse for storage and index size.
• If external identifiers are required, store UUID as BINARY(16) in a secondary unique column:

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  public_id BINARY(16) NOT NULL,
  UNIQUE KEY idx_public_id (public_id)
);
-- UUID_TO_BIN(uuid, 1) reorders UUIDv1 bytes to be roughly time-sorted (reduces fragmentation)
-- MySQL's UUID() returns UUIDv4 (random). For time-ordered IDs, use app-generated UUIDv7/ULID/Snowflake.
INSERT INTO users (public_id) VALUES (UUID_TO_BIN(?, 1)); -- app provides UUID string

If UUIDs are required, prefer time-ordered variants such as UUIDv7 (app-generated) to reduce index fragmentation.

Secondary Indexes Include the Primary Key

InnoDB secondary indexes store the primary key value with each index entry. Implications:

• Larger secondary indexes: a secondary index entry includes (indexed columns + PK bytes).
• Covering reads: SELECT id FROM users WHERE email = ? can often be satisfied from INDEX(email) because id (PK) is already present in the index entry.
• UUID penalty: a BINARY(16) PK makes every secondary index entry 8 bytes larger than a BIGINT PK.

Auto-Increment Considerations

• Hot spot: inserts target the end of the clustered index (usually fine; can bottleneck at extreme insert rates).
• Gaps are normal: rollbacks or failed inserts can leave gaps.
• Locking: auto-increment allocation can introduce contention under very high concurrency.

Alternative Ordered IDs (Snowflake / ULID / UUIDv7)

If you need globally unique IDs generated outside the database:

• Snowflake-style: 64-bit integers (fits in BIGINT), time-ordered, compact.
• ULID / UUIDv7: 128-bit (store as BINARY(16)), time-ordered, better insert locality than random UUIDv4.

Recommendation: prefer BIGINT AUTO_INCREMENT unless you need distributed ID generation or externally meaningful identifiers.

Replication Considerations

• Random-key insert patterns (UUIDv4) can amplify page splits and I/O on replicas too, increasing lag.
• Time-ordered IDs reduce fragmentation and tend to replicate more smoothly under heavy insert workloads.

Composite Primary Keys

Use for join/many-to-many tables. Most-queried column first:

CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id)
);

Query Optimization Pitfalls

These patterns look correct but bypass indexes or cause full scans.

Non-Sargable Predicates

A sargable predicate can use an index. Common non-sargable patterns:

• functions/arithmetic on indexed columns
• implicit type conversions
• leading wildcards (LIKE '%x')
• some negations (!=, NOT IN, NOT LIKE) depending on shape/data

Functions on Indexed Columns

-- BAD: function prevents index use on created_at
WHERE YEAR(created_at) = 2024

-- GOOD: sargable range
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

MySQL 8.0+ can use expression (functional) indexes for some cases:

CREATE INDEX idx_users_upper_name ON users ((UPPER(name)));
-- Now this can use idx_users_upper_name:
WHERE UPPER(name) = 'SMITH'

Implicit Type Conversions

Implicit casts can make indexes unusable:

-- If phone is VARCHAR, this may force CAST(phone AS UNSIGNED) and scan
WHERE phone = 1234567890

-- Better: match the column type
WHERE phone = '1234567890'

LIKE Patterns

-- BAD: leading wildcard cannot use a B-Tree index
WHERE name LIKE '%smith'
WHERE name LIKE '%smith%'

-- GOOD: prefix match can use an index
WHERE name LIKE 'smith%'

For suffix search, consider storing a reversed generated column + prefix search:

ALTER TABLE users
  ADD COLUMN name_reversed VARCHAR(255) AS (REVERSE(name)) STORED,
  ADD INDEX idx_users_name_reversed (name_reversed);

WHERE name_reversed LIKE CONCAT(REVERSE('smith'), '%');

For infix search at scale, use FULLTEXT (when appropriate) or a dedicated search engine.

OR Across Different Columns

OR across different columns often prevents efficient index use.

-- Often suboptimal
WHERE status = 'active' OR region = 'us-east'

-- Often better: two indexed queries
SELECT * FROM orders WHERE status = 'active'
UNION ALL
SELECT * FROM orders WHERE region = 'us-east';

MySQL can sometimes use index_merge, but it's frequently slower than a purpose-built composite index or a UNION rewrite.

ORDER BY + LIMIT Without an Index

LIMIT does not automatically make sorting cheap. If no index supports the order, MySQL may sort many rows (Using filesort) and then apply LIMIT.

-- Needs an index on created_at (or it will filesort)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- For WHERE + ORDER BY, you usually need a composite index:
-- (status, created_at DESC)
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

DISTINCT / GROUP BY

DISTINCT and GROUP BY can trigger temp tables and sorts (Using temporary, Using filesort) when indexes don't match.

-- Often improved by an index on (status)
SELECT DISTINCT status FROM orders;

-- Often improved by an index on (status)
SELECT status, COUNT(*) FROM orders GROUP BY status;

Derived Tables / CTE Materialization

Derived tables and CTEs may be materialized into temporary tables, which can be slower than a flattened query. If performance is surprising, check EXPLAIN and consider rewriting the query or adding supporting indexes.

Other Quick Rules

• `OFFSET` pagination: OFFSET N scans and discards N rows. Use cursor-based pagination.
• *`SELECT `** defeats covering indexes. Select only needed columns.
• `NOT IN` with NULLs: NOT IN (subquery) returns no rows if subquery contains any NULL. Use NOT EXISTS.
• *`COUNT() vs COUNT(col)**: COUNT(*) counts all rows; COUNT(col)` skips NULLs.
• Arithmetic on indexed columns: WHERE price * 1.1 > 100 prevents index use. Rewrite to keep the column bare: WHERE price > 100 / 1.1.

Replication Lag

MySQL replication is asynchronous by default. Reads from a replica may return stale data.

The Core Problem

1. App writes to primary: INSERT INTO orders ... 2. App immediately reads from replica: SELECT * FROM orders WHERE id = ? 3. Replica hasn't applied the write yet — returns empty or stale data.

Detecting Lag

-- On the replica
SHOW REPLICA STATUS\G
-- Key field: Seconds_Behind_Source (0 = caught up, NULL = not replicating)

Warning: Seconds_Behind_Source measures relay-log lag, not true wall-clock staleness. It can underreport during long-running transactions because it only updates when transactions commit.

GTID-based lag: for more accurate tracking, compare @@global.gtid_executed (replica) to primary GTID position, or use WAIT_FOR_EXECUTED_GTID_SET() to wait for a specific transaction.

Note: parallel replication with replica_parallel_type=LOGICAL_CLOCK requires binlog_format=ROW. Statement-based replication (binlog_format=STATEMENT) is more limited for parallel apply.

Mitigation Strategies

Common Pitfalls

• Large transactions cause lag spikes: A single INSERT ... SELECT of 1M rows replays as one big transaction on the replica. Break into batches.
• DDL blocks replication: ALTER TABLE with ALGORITHM=COPY on primary replays on replica, blocking other relay-log events during execution. INSTANT and INPLACE DDL are less blocking but still require brief metadata locks.
• Long queries on replica: A slow SELECT on the replica can block relay-log application. Use replica_parallel_workers (8.0+) with replica_parallel_type=LOGICAL_CLOCK for parallel apply. Note: LOGICAL_CLOCK requires binlog_format=ROW and slave_preserve_commit_order=ON (or replica_preserve_commit_order=ON) to preserve commit order.
• IO thread bottlenecks: Network latency, disk I/O, or relay_log_space_limit exhaustion can cause lag even when the SQL apply thread isn't saturated. Monitor Relay_Log_Space and connectivity.

Guidelines

• Assume replicas are always slightly behind. Design reads accordingly.
• Use GTID-based replication for reliable failover and lag tracking.
• Monitor Seconds_Behind_Source with alerting (>5s warrants investigation).

Row Locking Gotchas

InnoDB uses row-level locking, but the actual locked range is often wider than expected.

Next-Key Locks (REPEATABLE READ)

InnoDB's default isolation level uses next-key locks for locking reads (SELECT ... FOR UPDATE, SELECT ... FOR SHARE, UPDATE, DELETE) to prevent phantom reads. A range scan locks every gap in that range. Plain SELECT statements use consistent reads (MVCC) and don't acquire locks.

Exception: a unique index search with a unique search condition (e.g., WHERE id = 5 on a unique id) locks only the index record, not the gap. Gap/next-key locks still apply for range scans and non-unique searches.

-- Locks rows with id 5..10 AND the gaps between them and after the range
SELECT * FROM orders WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- Another session inserting id=7 blocks until the lock is released.

Gap Locks on Non-Existent Rows

SELECT ... FOR UPDATE on a row that doesn't exist still places a gap lock:

-- No row with id=999 exists, but this locks the gap around where 999 would be
SELECT * FROM orders WHERE id = 999 FOR UPDATE;
-- Concurrent INSERTs into that gap are blocked.

Index-Less UPDATE/DELETE = Full Scan and Broad Locking

If the WHERE column has no index, InnoDB must scan all rows and locks every row examined (often effectively all rows in the table). This is not table-level locking—InnoDB doesn't escalate locks—but rather row-level locks on all rows:

-- No index on status → locks all rows (not a table lock, but all row locks)
UPDATE orders SET processed = 1 WHERE status = 'pending';
-- Fix: CREATE INDEX idx_status ON orders (status);

SELECT ... FOR SHARE (Shared Locks)

SELECT ... FOR SHARE acquires shared (S) locks instead of exclusive (X) locks. Multiple sessions can hold shared locks simultaneously, but exclusive locks are blocked:

-- Session 1: shared lock
SELECT * FROM orders WHERE id = 5 FOR SHARE;

-- Session 2: also allowed (shared lock)
SELECT * FROM orders WHERE id = 5 FOR SHARE;

-- Session 3: blocked until shared locks are released
UPDATE orders SET status = 'processed' WHERE id = 5;

Gap/next-key locks can still apply in REPEATABLE READ, so inserts into locked gaps may be blocked even with shared locks.

INSERT ... ON DUPLICATE KEY UPDATE

Takes an exclusive next-key lock on the index entry. If multiple sessions do this concurrently on nearby key values, gap-lock deadlocks are common.

Lock Escalation Misconception

InnoDB does not automatically escalate row locks to table locks. When a missing index causes "table-wide" locking, it's because InnoDB scans and locks all rows individually—not because locks were escalated.

Mitigation Strategies

• Use READ COMMITTED when gap locks cause excessive blocking (gap locks disabled in RC except for FK/duplicate-key checks).
• Keep transactions short — hold locks for milliseconds, not seconds.
• Ensure WHERE columns are indexed to avoid full-table lock scans.