TL;DR

Last post we took time to go over simple SQL queries, Relational Models, and Normalization. Here we are going to cover what makes up an effective query, how concurrency control is placed within each query and how to recover data.

We have standards to associate with effective queries which should in turn be looked upon as transactions. These transactions should hold a certain state of behaviors known as the ACID principles.

When running a particular query/transaction in our database environment we must be mindful of such principles. We should look to consistently optimize how we do things as programmers, engineers or data analyst.

To note, the way we can go about effectively optimizing our database transactions is how we look at concurrency in our systems as well as handling fail over in the form of recovery.

Introduction

By the end of this tutorial, you will understand

• ACID properties
• Concurrency Control
• Database Recovery

Core Concepts

ACID

Atomicity, Consistency, Isolation, Durability

These properties make up an effective database transaction and are accepted principles to aim for in creating index's and other optimization techniques.

1. Atomicity states that a transaction takes place at once or doesn't happen at all.

2. Consistency states that a database must be consistent before and after a transaction.

3. Isolation states multiple transactions occur independently without interruption.

4. Durability states that changes from a transaction still occurs even in the event of a system failure.

Concurrency Control

Concurrency deals with the execution of multiple transactions.

Concurrency control is dealt with in a few ways but, locking is one way to effectively deal with concurrency control. Locking protocols are used to lock a data item in place during the event of a transaction/query.

These protocols can deal with multiple transactions whenever such transactions request a lock on that data item simultaneously. This is all managed with a mechanism known as a lock manager in our DBMS.

Database Recovery

Database Systems like computer systems are subjected to failure. In the event of these failures data still needs to be present and available when needed.

When looking at recovery techniques these items are all dependent on whats known as a system log.

It contains information about the start and end of each transaction and any updates which occur in the transaction.

Techniques:

1. Undoing: If failure occurs then the transaction manager will revert the previous transaction back to its original state.

2. Cache/Buffering: One or more disk pages including data to be updated are cached into memory buffers and then updated in memory before writing them back to the disk.

3. Backups: Occurs when data is copied over/backed up to a secondary source.

This concludes part 3 of this series, I will post more on part 4 shortly