SQL & MySQL Interview Questions 31-35 (Internals: Deadlocks, Engines, Caching)

Hello! In our previous lessons, we've covered how to structure and query databases. Now, we're going to look deeper under the hood.

This lesson is about the engine of the database. We'll cover what happens when concurrent requests collide (deadlocks), the different "engine types" that MySQL uses to store data (InnoDB vs. MyISAM), and the critical mechanisms that make MySQL fast, like the Buffer Pool. Understanding these concepts shows a mature grasp of how a database really works.

31. What is deadlock? How do you detect and fix it?

A deadlock is a concurrency problem where two (or more) transactions are stuck, each waiting for a resource that the other transaction holds. This creates a circular dependency where neither can proceed, and they wait forever.

Analogy: The Hallway Standoff
Imagine two people, Transaction A and Transaction B, meeting in a narrow hallway.

They are in a deadlock. Neither can move, and they will be stuck until someone (the database) intervenes.

SQL Deadlock Example:

Imagine two transactions running at the same time:

-- Transaction 1
START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
-- Now it wants to update row 2
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2 (at the same time)
START TRANSACTION;
UPDATE Accounts SET balance = balance - 50 WHERE id = 2;
-- Now it wants to update row 1
UPDATE Accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

How it happens:

How to Detect and Fix:

32. What are MySQL storage engines?

A storage engine is the underlying software component that MySQL uses to create, read, update, and delete data (CRUD) in a table.

Analogy: Think of MySQL Server as a car chassis. The storage engine is the engine you choose to put in it.

MySQL lets you choose the engine per table, so you can mix and match. However, InnoDB is the default and the best choice for 99.9% of modern applications.

-- Create a table using the default InnoDB engine
CREATE TABLE MyTable1 (id INT) ENGINE=InnoDB;

-- Create a table using the older MyISAM engine
CREATE TABLE MyTable2 (id INT) ENGINE=MyISAM;

-- See the storage engine for a table
SHOW TABLE STATUS LIKE 'MyTable1';

Sample Output (from `SHOW...`):

+---------+--------+ ...
| Name    | Engine | ...
+---------+--------+ ...
| MyTable1| InnoDB | ...
+---------+--------+ ...
1 row in set

33. What is the difference between InnoDB and MyISAM?

This is the most common storage engine comparison. The short answer is that InnoDB is the modern, reliable default, and MyISAM is the legacy engine that you should generally avoid.

Tip: The key difference to remember is locking. If you update one row in a MyISAM table, the entire table is locked, and no one else can even read from it until you are done. In InnoDB, only the single row is locked. This makes InnoDB infinitely better for a web application with many concurrent users.

34. Explain the concept of the MySQL buffer pool.

The InnoDB buffer pool is the most important memory area for an InnoDB database. It's a large cache in RAM where InnoDB holds copies of both data pages and index pages that it has recently accessed from disk.

Analogy: A Carpenter's Workbench
Imagine your database is a giant warehouse of wood (the hard disk, which is slow). You are a carpenter (the MySQL server).

Reading from RAM (the buffer pool) is thousands of times faster than reading from disk. Therefore, the #1 goal of performance tuning a MySQL server is to make the buffer pool large enough to hold the "working set" (the most frequently accessed data and indexes) of your application.

35. What is MySQL Query Cache? Why is it deprecated?

The Query Cache was a feature in older MySQL versions (deprecated in 5.7, removed in 8.0) that tried to improve performance by caching the literal text of a `SELECT` query and itsexact result set.

Analogy: Imagine a simple, lazy cashier.

Why it was deprecated:

The query cache sounds good in theory, but it was terrible in practice, especially for modern web applications.