Inside the Database: Query To Execution Explained

Introduction

In this documentation, how a database processes a query from the time it is received in database from the client to its fully execution . The explanation is based on the components shown in the diagram, including the Network Layer, Frontend, Execution Engine, Transaction Management, Concurrency Manager, and Storage Manager.

Note: I am writing this for my understanding , if you also understood it's a win win. I learned it from YouTube . SOURCE

Flow of Execution

1. Client to Network Layer

The client sends a request (query) to the database server over a network using a communication protocol like TCP/IP.
The Network Layer is responsible for receiving the incoming query request through network. It manages the connection between the client and the database server.

2. Frontend
When client query the database, it is processed by frontend in following stages :

Tokenizer:
Breaks the query into smaller components (tokens) for easier processing.
For example, in SELECT * FROM users, tokens are SELECT, , FROM, and users.
*Parser:**
Verifies the syntax of the query to ensure it follows the correct format.
If there is an error, the query is rejected, and an error message is sent to the client.
Optimization:
It Generates the execution plan in the most efficient way to execute the query.
The optimizer considers factors like:
Availability of indexes.
How tables are joined or filtered.

1. Execution by the Execution Engine The optimized query is passed to the Execution Engine for execution. Here’s what happens:

Query Execution:

Executes the query according to the optimized execution plan.

Cache Manager:

If frequently used data exists in the cache, it is retrieved to speed up execution.
This reduces the need for disk reads, improving performance.

Utility Services:
Performs additional tasks such as sorting, aggregating, or formatting the query results.

4. Transaction Management

If the query involves changes to data (e.g., INSERT, UPDATE, or DELETE), the Transaction Manager ensures the following:

Lock Manager:
Locks specific rows, tables, or resources to prevent data conflicts during concurrent operations.

Recovery Manager:
Ensures changes are durable and can be recovered in case of a system crash.
Transactions adhere to ACID properties:

1. Atomicity: All parts of the transaction succeed or fail together.
2. Consistency: Ensures data remains in a valid state.
3. Isolation: Prevents interference from other transactions.
4. Durability: Changes are permanent after a transaction is committed.

5. Concurrency Management

In a multi-user environment, multiple queries may run simultaneously.
The Concurrency Manager handles:
Parallel Execution: Allowing multiple reads and writes without data conflicts.
Techniques like locking, versioning, or optimistic concurrency control ensure consistency.

6. Storage Management

The Storage Manager interacts with the underlying storage system to fetch or write data as required:

Disk Storage Manager:
Reads or writes data to and from the physical disk.

Buffer Manager:
Temporarily caches data in memory to speed up access to frequently used records.

Index Manager:
Uses indexes to locate specific rows quickly, avoiding full table scans.

7. OS Interaction Layer

The Storage Manager interacts with the Operating System for file I/O operations.
This ensures that data is read or written efficiently to the disk.

Summary of Execution Flow

• Client sends a query → received by Network Layer.
• Frontend processes the query (Tokenization → Parsing → Optimization).
• Execution Engine executes the query, retrieves or manipulates data.
• Transaction Manager ensures data consistency for write operations.
• Concurrency Manager handles simultaneous query execution.
• Storage Manager fetches or writes data from/to storage.
• Results are sent back to the client.