Durgesh Tiwari
Author
Most developers interact with databases using SQL queries, but they rarely think about what happens behind the scenes. Every time a query is executed, the database performs several internal operations to store data, retrieve records, manage transactions, and maintain consistency.
Database Internals refers to the internal processes and data structures that enable a database to store, retrieve, and manage data efficiently. Understanding these concepts helps developers design faster, more reliable, and scalable applications.
Whether you're building a banking system, social media platform, or e-commerce application, knowing how a database works internally helps you make better system design decisions.
As applications grow, databases must handle large amounts of data and many users at the same time. Efficient internal mechanisms help maintain fast performance, reliable transactions, and consistent data.
Understanding Database Internals helps developers:
Improve database performance.
Design scalable systems.
Handle concurrent users efficiently.
Maintain data consistency.
Prevent data corruption.
Recover from failures.
Build reliable applications.
Popular databases such as MySQL, PostgreSQL, Oracle, and SQL Server use advanced internal mechanisms to process data efficiently.
A database does not store data as plain text. Instead, it organizes information into pages (also called blocks) to make storage and retrieval more efficient.
A page is the basic unit used to read and write data. Each page contains multiple rows, allowing the database to process data in blocks instead of one row at a time.
When a new record is inserted, it is stored in an available page. When data is requested, the database reads only the required pages into memory instead of scanning the entire table. This reduces disk access and improves query performance.
Example
Suppose a Customers table contains 1 million records.
Instead of reading the entire table, the database loads only the pages containing the requested customer record.
This reduces disk I/O and allows the query to execute much faster.
A Storage Engine is the part of a database that stores, retrieves, and manages data on disk.
Different storage engines are designed for different workloads. Some are optimized for transactions, while others provide faster read or write performance.
In simple words, a Storage Engine decides how data is stored, managed, and accessed inside a database.
InnoDB is the default MySQL Storage Engine and is widely used for transactional applications.
Features:
Supports ACID transactions.
Uses row-level locking.
Provides crash recovery.
Supports foreign keys.
Ideal for transactional applications.
MyISAM is an older MySQL Storage Engine that is optimized for read-heavy workloads.
Features:
Fast read performance.
Uses table-level locking.
Does not support transactions.
Does not support foreign keys.
RocksDB is designed for applications that require high write performance and efficient storage.
It is commonly used in distributed systems and large-scale data processing.
The choice of a Storage Engine directly affects database performance and reliability.
A storage engine influences:
Query performance.
Transaction support.
Locking behavior.
Storage efficiency.
Crash recovery.
Overall scalability.
Example: An online banking system typically uses InnoDB because it supports ACID transactions, ensuring reliable and consistent data.
When multiple users access a database at the same time, they may try to read or update the same data. Without proper control, this can lead to incorrect or inconsistent results.
Database Locking is a mechanism that controls access to data while a transaction is being executed. It prevents multiple transactions from modifying the same data simultaneously, ensuring safe and reliable database operations.
In simple words, a database lock temporarily restricts access to data until the current transaction is completed.
Database Locking helps maintain data consistency and prevents conflicts when multiple transactions access the same data.
Some key benefits are:
Prevents data corruption.
Maintains data consistency.
Ensures reliable transactions.
Controls concurrent access.
Protects shared data.
Without Database Locking, multiple transactions could update the same record simultaneously, leading to incorrect or inconsistent data.
When multiple users access the same data at the same time, the database uses different types of locks to prevent conflicts and maintain data consistency.
The two most common database locks are:
Shared Lock (Read Lock)
Exclusive Lock (Write Lock)
A Shared Lock allows multiple users to read the same data at the same time. However, while the lock is active, the data cannot be modified.
In simple words, multiple users can read the data together, but no one can change it until the lock is released.
Example
Suppose several customers are viewing the same product on an e-commerce website.
All users can read the product details simultaneously because reading the data does not modify it.
Product Record
│
┌─────────┼─────────┐
▼ ▼ ▼
User A User B User C
Read Read Read
✔ Multiple reads allowed
✖ No updates allowedAllows multiple read operations.
Prevents data modification while the lock is active.
Improves concurrent read access.
Commonly used for SELECT queries.
An Exclusive Lock is applied when a transaction needs to insert, update, or delete data. While the lock is active, no other transaction can modify the same record.
In simple words, only one transaction can change the data at a time.
Example
Suppose a customer updates their profile information.
While the update is in progress, the database locks that record so no other transaction can modify it until the update is complete.
Customer Record
│
▼
Update Request
│
Exclusive Lock
│
Update Completed
│
Lock ReleasedAllows only one write operation at a time.
Prevents simultaneous updates.
Maintains data consistency.
Commonly used for INSERT, UPDATE, and DELETE operations.
A database can apply locks at different levels based on the amount of data being accessed. Choosing the right lock level helps balance performance and concurrency.
A Row-Level Lock locks only the specific row being modified, while the remaining rows stay available for other transactions.
Benefits:
Better concurrency.
Improves performance in multi-user applications.
Allows multiple users to update different rows simultaneously.
Commonly Used In:
MySQL (InnoDB)
PostgreSQL
A Table-Level Lock locks the entire table during an operation. While the lock is active, other transactions must wait before modifying data in that table.
Benefits:
Simple locking mechanism.
Useful for small tables or bulk operations.
Limitation:
Reduces concurrency because the whole table remains locked until the transaction completes.
A Deadlock occurs when two or more transactions wait for each other to release locked resources. Since each transaction is waiting for another, none of them can continue.
In simple words, a Deadlock happens when transactions block each other, causing all of them to stop.
Example
Suppose two transactions are running at the same time.
Transaction A
Locks the Customer table.
Waits for the Order table.
Transaction B
Locks the Order table.
Waits for the Customer table.
Since both transactions are waiting for each other, neither can continue.
Transaction A
Customer Table 🔒
│
▼
Waiting for Order Table
Transaction B
Order Table 🔒
│
▼
Waiting for Customer TableThis situation is called a Deadlock.
Deadlocks usually occur when:
Multiple transactions access the same resources.
Resources are locked in different orders.
Transactions hold locks for a long time.
Multiple tables are updated in a single transaction.
Most modern databases detect deadlocks automatically.
When a deadlock occurs, the database:
Detects the deadlock.
Selects one transaction as the victim.
Rolls back the victim transaction.
Releases its locks.
Continues the remaining transaction.
This prevents the database from remaining blocked and keeps transactions running smoothly.
MVCC (Multi-Version Concurrency Control) is a technique used by modern databases to improve concurrency by allowing multiple transactions to access the same data at the same time.
Instead of locking a record for every read operation, the database creates multiple versions of the same row. This allows read and write operations to run simultaneously with fewer conflicts and better performance.
In simple words, MVCC lets users read data without waiting for write operations to finish.
When a transaction updates a record:
A new version of the row is created.
The previous version is kept temporarily.
Running transactions continue reading the old version.
New transactions read the updated version after it is committed.
This approach reduces locking and improves concurrent database access.
Example
Suppose a customer's account balance is £1,000.
Transaction A starts and reads the balance.
Transaction B updates the balance to £1,200.
With MVCC:
Transaction A continues reading £1,000 until it finishes.
After Transaction B is committed, new transactions read £1,200.
Both transactions execute without blocking each other.
MVCC offers several benefits:
Reduces locking.
Improves concurrent access.
Makes read operations faster.
Minimizes transaction waiting time.
Improves overall database performance.
Databases such as PostgreSQL, MySQL InnoDB, and Oracle use MVCC to efficiently handle multiple users and transactions.
When a database processes transactions, unexpected events like server crashes, power failures, or hardware issues can interrupt the process. If changes are written directly to the database and a failure occurs, data may become inconsistent.
To prevent this, modern databases use Write-Ahead Logging (WAL).
Write-Ahead Logging (WAL) is a recovery technique where every change is first recorded in a log file before it is written to the database.
In simple words, WAL creates a backup record of every change before updating the actual data, making recovery possible after a failure.
Whenever a transaction is executed, the database follows these steps:
The transaction modifies the data.
The changes are written to the Write-Ahead Log (WAL).
The log is safely stored on disk.
The actual database pages are updated.
Once everything is successful, the transaction is committed.
If the system crashes before the data pages are updated, the database uses the WAL to restore the pending changes after restart.
Example
Suppose a customer transfers £500 from one bank account to another.
Instead of updating the account balances immediately:
The transfer details are first written to the WAL.
The log is stored safely.
The account balances are updated afterwards.
If the server crashes before the balance update completes, the database reads the WAL during recovery and finishes the transaction without losing data.
WAL offers several important benefits:
Prevents data loss during system failures.
Supports fast crash recovery.
Maintains data consistency.
Improves transaction reliability.
Ensures committed transactions remain safe.
Modern databases like PostgreSQL and MySQL InnoDB use Write-Ahead Logging (WAL) to provide reliable and fault-tolerant transaction processing.
As the database runs, the Write-Ahead Log (WAL) keeps growing. If the database had to process the entire log after a crash, recovery would take much longer.
To avoid this, databases create Checkpoints at regular intervals.
A Checkpoint saves modified data from memory to disk and marks a recovery point in the log.
In simple words, a Checkpoint reduces database recovery time by creating a safe point from which recovery can start.
When a checkpoint is created, the database:
Writes modified pages from memory to disk.
Records the current position in the Write-Ahead Log (WAL).
Marks a safe recovery point.
Continues normal database operations.
After a crash, the database starts recovery from the latest checkpoint instead of processing the entire log.
Checkpoints improve database performance and reliability by:
Reducing recovery time.
Minimizing log processing during recovery.
Keeping data synchronized between memory and disk.
Improving overall database reliability.
Even the most reliable databases can face failures due to hardware issues, software bugs, power outages, or system crashes.
Database Recovery is the process of restoring a database to a correct and consistent state after a failure. Its main goal is to protect committed data and remove incomplete transactions.
In simple words, Database Recovery restores the database after a failure without losing committed data.
When the database restarts after a crash, it follows these steps:
Reads the latest Checkpoint.
Loads the Write-Ahead Log (WAL).
Reapplies committed transactions that were not written to disk.
Rolls back incomplete transactions.
Restores the database to a consistent state.
This process allows the database to recover automatically while protecting important data.
Database Recovery helps to:
Prevent data loss.
Restore the database after failures.
Preserve committed transactions.
Roll back incomplete transactions.
Maintain data consistency.
Improve system reliability.
Database recovery is mainly divided into two types.
Crash Recovery is performed after unexpected system failures, such as:
Power failure
Server crash
Operating system failure
The database uses Checkpoints and Write-Ahead Logging (WAL) to recover committed data and restore the database.
Media Recovery is required when the storage device itself is damaged or data files are lost.
Common causes include:
Hard disk failure
SSD corruption
Accidental data file deletion
In this case, the database restores data using backups and transaction logs.
Consider a cloud storage platform.
Millions of users upload, edit, and access files every day. To handle these operations safely, the database uses MVCC to allow multiple users to read and update data without unnecessary locking.
If a server unexpectedly crashes, Write-Ahead Logging (WAL) and Checkpoints help recover committed changes, while Database Recovery restores the database to a consistent state.
This combination ensures high performance, reliable data recovery, and uninterrupted service even during unexpected failures.
Database Internals explain how a database stores data, manages concurrent users, and recovers from failures. Concepts like Storage Engines, Database Locking, Deadlocks, and MVCC help improve performance while maintaining data consistency.
To protect data during failures, databases use Write-Ahead Logging (WAL) to record changes before updating the database. Checkpoints reduce recovery time, and Database Recovery restores the database to a consistent state after a failure.
Together, these concepts help build fast, reliable, and scalable database systems that can efficiently handle concurrent users and unexpected failures.