Mastering Database Internals for Go Interviews: Indexes, Transactions, Locks, MVCC & Connection Pools

In the third part of the "Go interview trilogy," the focus shifts from basic CRUD to the underlying database concepts that separate mid‑level engineers from senior engineers at large tech companies.

You may have written Go for three years, yet never truly understood how indexes, transactions, locks, or connection pools affect online system performance.

Interviewers know that lacking this knowledge is a common reason candidates get stuck on database questions.

Chapter 1 – Why Indexes Speed Up Queries (and When They Don’t)

Typical explanation: "Indexes are B+Trees that reduce disk I/O." The real essence is that an index reduces row scans, letting MySQL skip unnecessary work.

Root node is only a few KB.

Tree height is usually 2–4 levels.

A range query may need only dozens of node accesses.

Values map to primary keys, then to data pages.

SQL performance depends on whether the query can leverage the index to cut scanned rows.

Why a Query Can Still Be Slow with an Index

SELECT * FROM order WHERE status = 1;

Even with an index on status, the query may be slow because the column’s selectivity is low (most rows have status = 1), causing MySQL to prefer a full table scan.

Interviewers often ask how to judge index effectiveness. Run EXPLAIN SELECT ...; and examine:

possible_keys

key

rows

(estimated rows scanned) type (optimization level)

To improve low‑selectivity columns, create a composite index, e.g., (status, create_time), so MySQL can filter by create_time first.

Chapter 2 – Transactions and Isolation Levels

High‑concurrency services often suffer from duplicate orders, oversold inventory, inconsistent wallet deductions, or stale reads—issues rooted in insufficient understanding of transactions and isolation.

Real‑world Example: Preventing Oversell

db.Exec("UPDATE product SET stock = stock - 1 WHERE id = ? AND stock > 0;")
affected, _ := res.RowsAffected()
if affected == 0 {
    // Stock insufficient, update failed
}

If the condition stock > 0 is omitted, two concurrent transactions may both see stock = 1 and decrement, causing oversell.

Isolation Levels and Their Effects

Read Uncommitted – dirty reads

Read Committed – non‑repeatable reads

Repeatable Read (default) – prevents non‑repeatable reads

Serializable – worst performance

In Repeatable Read, MySQL uses MVCC to avoid locking while guaranteeing repeatable reads.

Why RR Prevents Non‑Repeatable Reads

Each row has multiple versions (undo log).

SELECT uses snapshot reads.

The same transaction sees the same version throughout.

Other transactions' UPDATEs do not affect the snapshot.

Chapter 3 – Lock Mechanisms

Beyond simple read/write locks, MySQL implements several lock types:

Table lock

Row lock

Gap lock

Next‑Key lock (row + gap)

Intent lock

Example: SELECT * FROM user WHERE age > 30 FOR UPDATE; locks the gap between age = 30 and the next existing row to prevent phantom reads.

Chapter 4 – MVCC (Multi‑Version Concurrency Control)

MVCC’s primary engineering benefit is allowing reads without acquiring locks, reducing database pressure. About 80 % of queries are snapshot reads.

Each row stores multiple versions.

SELECT reads a snapshot, no lock.

UPDATE acquires a write lock to ensure single‑writer consistency.

Undo log enables rollback.

| row | trx_id | roll_pointer | data |
|---- |--------|--------------|------|

undo_log -> older_version -> older_version

Chapter 5 – Connection Pooling

Performance bottlenecks often stem from exhausted or mis‑configured connection pools rather than slow SQL.

Pool exhaustion

Improper connection reuse

Connection leaks

Long‑running transactions holding connections

Typical symptom: many goroutines block waiting for idle connections, MySQL reports insufficient idle connections, and MaxOpenConns is set too low.

db.SetMaxOpenConns(200)
db.SetMaxIdleConns(50)
db.SetConnMaxLifetime(30 * time.Minute)

Do not set MaxOpenConns too low.

Too few idle connections cause frequent reconnections.

Setting a reasonable lifetime avoids MySQL‑forced disconnections.

Chapter 6 – GORM Performance Pitfalls

ORM performance hinges on how efficiently it maps objects to SQL and back.

Default queries load all columns and pre‑load field metadata.

Optimization strategies:

Select only needed fields.

Use raw SQL for critical paths.

Prefer Preload over Join when appropriate.

db.Select("id", "name").Find(&users)

db.Raw("SELECT id, name FROM user WHERE id = ?", id).Scan(&user)

High‑Frequency Interview Questions

Why can an indexed query still be slow? – Check selectivity, execution plan, and possible index misuse.

How does RR isolation avoid non‑repeatable reads? – Snapshot reads via MVCC plus Next‑Key locks.

How to prevent inventory oversell under high concurrency? – Use atomic UPDATE … WHERE stock > 0 and verify RowsAffected.

What happens when Go connection pool is exhausted and how to diagnose? – Goroutines block, check pprof/metrics, ensure proper MaxOpenConns, MaxIdleConns, and connection release.

Mastering these topics transforms a developer from merely writing business code to confidently handling database performance, consistency, and scalability in real‑world Go services.