Inside the DBMS: Components and How a Query Flows

Concept

Phase 1 named the components. This note opens them up and follows a single request from arrival to result, because the order in which the components act is what most exam answers are actually testing.

The component map

                    request arrives
                          |
   +----------------------v-----------------------+
   | QUERY PROCESSOR                               |
   |   DDL compiler      definitions -> catalog    |
   |   DML compiler      parse and translate       |
   |   optimiser         choose the cheapest plan  |
   |   execution engine  run the chosen plan       |
   +----------------------+-----------------------+
                          |
   +----------------------v-----------------------+
   | STORAGE MANAGER                               |
   |   authorisation manager  may this happen?     |
   |   integrity manager      does it break a rule?|
   |   transaction manager    atomicity, isolation |
   |   buffer manager         memory pages         |
   |   file manager           disk space, records  |
   +----------------------+-----------------------+
                          |
   +----------------------v-----------------------+
   | DISK                                          |
   |   data files   index files   log file         |
   |   data dictionary (the catalog)               |
   |   statistical data (for the optimiser)        |
   +-----------------------------------------------+

What each component does

Following one query end to end

Request: the marks of one student

 1  CONNECTION       the session is identified
 2  AUTHORISATION    may this account read enrolments? yes
 3  PARSE            the request is checked for syntax and
                     every table and column is looked up in
                     the CATALOG. Unknown column -> rejected here
 4  OPTIMISE         statistics say enrolments has 400,000 rows
                     and an index exists on roll_no, so an index
                     lookup is chosen over a full scan
 5  EXECUTE          the engine asks for the index pages
 6  BUFFER           two pages are already in memory (a hit),
                     one must be fetched (a miss)
 7  FILE             the missing page is read from disk
 8  RESULT           matching rows are assembled and returned
 9  TRANSACTION      a read only statement commits trivially

Now the same request as an UPDATE of one mark:

 3b INTEGRITY        the check constraint 0 to 100 is verified
 4b LOCK             the transaction manager takes an exclusive
                     lock on that row
 5b LOG              the recovery manager writes the old and new
                     values to the LOG BEFORE the page is changed
                     - this is write ahead logging
 6b MODIFY           the page is changed in the buffer, not yet
                     on disk
 7b COMMIT           the log is forced to disk. The data page may
                     still be in memory. Durability comes from
                     the LOG, not from the data page

Step 7b is the single most valuable idea in this note. A committed transaction is durable because its log records are safely on disk, not because its data pages are. That is why recovery works, and it is a favourite interview question.

Where the catalog is used

• Parsing — do these tables and columns exist, and what are their types?
• Authorisation — what privileges does this account hold?
• Integrity — what constraints apply?
• Optimisation — how many rows, how many distinct values, which indexes exist?

Four components read the catalog before a single row is touched. That is what self describing means in practice.

Common mistakes

• Putting the optimiser before the parser. Nothing can be optimised until it has been parsed and validated against the catalog.
• Believing commit writes the data pages to disk. Commit forces the log. Data pages are written later by the buffer manager.
• Merging the buffer manager and the file manager. One manages memory, the other manages disk.
• Forgetting authorisation happens early. A privilege check after execution would already have leaked the data.

Exam and interview questions

1. Draw the internal structure of a DBMS and label every component.
2. Trace a select and an update through the components, in order.
3. What is write ahead logging, and why must the log reach disk before the data page changes?
4. Name four components that read the system catalog and say what each needs from it.
5. Which component would you suspect if queries are correct but suddenly slow?

Practice

1. Write the ordered list of components touched by a delete statement.
2. Explain in three sentences why durability depends on the log rather than the data file.
3. For each symptom, name the component: invalid data accepted, unauthorised read succeeded, crash left half a transaction, every query reads from disk.

Conclusion

The query processor decides what to do, the storage manager does it safely, the transaction and recovery managers make it correct under concurrency and failure, and all of them read the catalog first. Phase 14 returns to the optimiser in depth, and Phase 11 to the log.