Outline

• Storage and Buffer Management
• Transaction Processing
• In-Class Exercise
• Query Processor

Introduction

• Last day, we showed a diagram of the components and typical flow of control in a database management system (DBMS).
• We then considered the kinds of interactions a database administrator (DBA) might have with such a system and the flow through this diagram of data definition language (DDL) commands a DBA might issue.
• Next we considered the kinds of commands a user or application program might issue on a DBMS and how these queries or transactions might proceed with respect to our diagram.
• We start today by looking at the storage and buffer manager needed for query and transaction processing and then look at the systems needed for transaction processing in more detail.

Storage and Buffer Management

• Data in a database usually resides in secondary storage (disk/SSD).
• To perform useful operations on data, the data must be in main memory (RAM).
• It is the job of the DBMS' storage manager to control the placement of data on disk and control its movement between disk and main memory.
• The storage manager keeps track of the location of files on the disk and obtains block or blocks from a file based on requests from the buffer manager.
• The buffer manager is responsible for partitioning the main memory the DBMS has access to into buffers.
• Buffers are page-sized regions (a page is a standard number of bytes of main memory) into which disk blocks (a block is standard number of bytes that are read/written to the disk in one go) can be transferred.
• All DBMS components that need disk info will interact either directly or indirectly with the buffer manager.

DBMS Info Stored on Disk

The following DBMS info is persisted on disk and hence would involve requests through the buffer and storage manager for to access and manipulate:

1. Data: the contents of the database itself.
2. Metadata: the database schema that describes the structure of and constraints on the database.
3. Log Records: information about recent changes to the database; these are used to make ensure the durability of data stored in the database.
4. Statistics: information gathered and stored by the DBMS such as sizes of various relations, etc.
5. Indexes: data structures used to help speed up access to data in the database.

Transaction Processing

• Often one or more database operations are group together into what's called a transaction.
• For example, read an account balance, change its value, write an account balance.
• A transaction is a unit of work that must be executed atomically and in apparent isolation from other transactions in order to ensure the consistency of the data in the database.
• A DBMS should ensure that the work of a completed transaction is never lost (it is durable).
• The transaction manager accepts commands from a user/application. For example, begin transaction, end transaction, and various kinds of read/write commands.
• It also allows the user/application to set the expectation for how these commands must operate. For example, some transactions might be more or less strict about their atomicity requirements.

ACID Properties of a Transaction

Properly implemented transactions are often said to meet the "ACID" test where:

• "A" stands for "atomicity". Either all of the effects of the transaction are permanently recorded to the DB when the transaction completes or none of them are.
• "C" stands for "consistency". Given a database (DB) that meets all constraints and expectations, the results of a transaction should be a DB in which consistency constraints still hold and expectations about relationships of data are still valid.
• "I" stands for "isolation". Each transaction should appear to be executed as if no other transaction is executing at the same time.
• "D" stands for "durability". The effect of a transaction should never be lost once the transaction completes.

Tasks of the Transaction Processor

• Logging: To ensure durability, every change in the database is logged separately to disk. The log manager follows one of several policies to assure that no matter when a system failure (aka crash) occurs, a recovery manager will be able to examine the log of changes and restore the database to a consistent state.
• Concurrency Control: Transactions must appear to execute in isolation. On most systems, however, many transactions will be executing at the same time. The scheduler/concurrency control manager must assure that the individuals actions of multiple transactions are executed in such an order that the net effect is the same as if the transactions had operated one at a time in some order. Schedulers often do this by maintaining a lock table which has rows consisting of a transaction id and a database item (row, page, table, etc) that a transaction has the lock for. In order to schedule an operation on an item with the execution engine, the scheduler requires a transaction to first get a suitable lock on that item. By controlling, when locks are released, the scheduler can ensure isolation.
• Deadlock Resolution: As transactions compete for resources through the locks the scheduler grants, one can get into a situation where some group of transactions cannot proceed because each transaction in it needs a lock that the another transaction in the group has. The transaction manager then must intervene and decide to abort some transactions to allow others to proceed.

In-Class Exercise

Come up with a situation involving transactions with school records and transactions involving airline reservations in which deadlock occurs. Write a few sentences describing your situations.

Post your solution to the Aug. 28 In-Class Exercise Thread.

Query Processor

• The query processor has two main components:
  1. The query compiler: This translates the query into an internal form called a query plan, a sequence of operations to be performed on the data. Often these plans are a sequence of "relational algebra" operations. The query compiler has three major sub-parts:
     1. A query parser: This builds a tree data structure from the text (SQL) form of the query.
     2. A query preprocessor: This performs semantic checks on the tree (do all specified relations exists?, etc.). It might also do simple tree transformations to turn the parse tree into a tree of algebraic operators representing the initial query plan.
     3. A query optimizer: This transforms the initial query plan into the best available sequence of operations on the actual data. For example, this might involve choosing if there is more than one index available on the data which to use. It might involve choosing one of many algorithms to use based on table size statistics, etc.
  2. The execution engine: This is responsible for executing each of the steps of the chosen query plan. This component interacts with other components directly or through the buffers to manipulate the data. It also interacts with the scheduler to avoid accessing locked data, and with the log manager to make sure all the DB changes are properly logged.