SQL & MySQL Interview Questions 26-30 (Scaling, Transactions & Concurrency)

Welcome to the next lesson on database concepts! We've covered how to structure, query, and optimize tables. Now, we're going to focus on two of the biggest topics in database engineering: scaling (how to handle massive amounts of data) and reliability (how to make sure your data is always safe and correct).

These concepts—sharding, partitioning, transactions, ACID, and locking—are the foundation of any large-scale, professional application. Understanding them will show your interviewer you aren't just thinking about code, but about building robust systems.

26. What is database sharding?

Sharding is a database architecture technique for horizontal scaling. It involves splitting one, massive logical database into multiple, smaller, and more manageable pieces called "shards."

Crucially, these shards are spread across multiple physical machines (servers).

Analogy: Imagine a single, colossal library (one database server) that has 50 billion books. It's so big that the check-out line is hours long, and the building is about to collapse.

Sharding is the decision to build three separate library buildings (three different servers) across the city.

When a user comes to check out a book, the application (the librarian) first looks at their `user_id` (the "shard key") and sends them to the correct building.

27. What is partitioning in MySQL? Explain types of partitioning.

Partitioning is a concept that looks like sharding, but it all happens on a single database server.

You take one very large table and split it into smaller, physical sub-tables (partitions), but to your application, it still looks like a single table.

Analogy: Sharding was building new libraries. Partitioning is taking your one massive library building (one server) and organizing its one giant, messy room (one table) into separate, labeled sections (partitions).

For example, you partition your `Orders` table by year. The database creates hidden sub-tables: `Orders_2023`, `Orders_2024`, `Orders_2025`.

Why is this useful? When you run a query like `SELECT * FROM Orders WHERE order_date = '2024-05-15'`, the database is smart enough to only scan the `Orders_2024` partition. This is called "partition pruning" and it's extremely fast.

Common Types of Partitioning:

# Example of creating a RANGE-partitioned table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_2025 VALUES LESS THAN (2026),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- When you run this, MySQL is smart enough
-- to only search in the 'p_2024' partition.
EXPLAIN SELECT * FROM Orders 
WHERE order_date = '2024-03-01';

28. What are transactions in MySQL?

A transaction is a way to group one or more SQL commands into a single, all-or-nothing unit of work.

This is one of the most important concepts for data reliability.

Analogy: A Bank Transfer
Imagine you want to transfer $100 from your Savings account to your Checking account. This is two separate `UPDATE` commands:

What happens if the database server crashes right after step 1, but before step 2? The $100 is gone. Your database is now corrupt and inconsistent.

A transaction prevents this. By wrapping both commands in a transaction, you tell the database: "You must do both of these things successfully. If anything fails (even a power outage), you must undo all of it."

The key commands are:

START TRANSACTION;

-- Step 1: Subtract from Savings
UPDATE Savings SET balance = balance - 100 WHERE user_id = 123;

-- Step 2: Add to Checking
UPDATE Checking SET balance = balance + 100 WHERE user_id = 123;

-- If we get here with no errors, save the changes.
COMMIT;

-- If there was an error, a CATCH block in an application
-- would run this command instead:
-- ROLLBACK;

29. Explain ACID properties with examples.

ACID is an acronym for the four guarantees that a good transactional database (like MySQL's InnoDB engine) provides.

These are the rules that make transactions reliable. We can use our bank transfer analogy for all of them.

30. What is locking in MySQL? Explain shared and exclusive locks.

Locking is the mechanism that the database uses to enforce the 'I' (Isolation) in ACID. It's how the database prevents two transactions from interfering with each other and corrupting data.

Analogy: Think of a spreadsheet on a shared drive.

In general, MySQL (InnoDB) handles this automatically:

You can also explicitly ask for a stronger lock in your query:

START TRANSACTION;

-- This is a "pessimistic" lock.
-- It acquires an EXCLUSIVE (Write) lock on row 123.
-- It says, "I am going to update this row,
-- so don't let *anyone* else even read it
-- until I am done."
SELECT * FROM Savings WHERE user_id = 123 FOR UPDATE;

-- ... application logic ...

UPDATE Savings SET balance = balance - 100 WHERE user_id = 123;

COMMIT;

Tip: Understanding ACID properties is non-negotiable for a backend or full-stack role. Be sure you can explain Atomicity and Isolation with a simple, clear example like the bank transfer.