A database, as you may already know, is a system that stores and analyzes data. Databases are used for various platforms and purposes-- in schools, businesses, large-scale companies, etc.

Data/information is important for anyone, and databases are responsible for holding that crucial information. Hence, the security, integrity, and consistency of all values within them must be ensured. For this purpose, databases should have some specific properties. This tutorial discusses one such property of them, that of isolation.

To understand database isolation, you need to understand database systems fundamentals first. Let's review some facts about DBMS!

Before we begin to discuss isolation, it is important to learn about some terminologies. Let's dive into transactions, concurrency, and read phenomena.

Transactions

Transactions refer to a collection of several small operations that aim to perform a single operation. In DBMS terms, a single transaction runs multiple queries on a database system to complete a task.

Let's understand this with an example. Suppose you want to transfer funds from one bank account to another. From the perspective of a user, it's a simple task, as you only need to select the "funds transfer" option. However, from the database's perspective, a series of queries will get run, like checking the balance, updating the sender's bank balance, updating the receiver's bank balance, and generating a transaction record. All these small operations make up a single transaction.

An important responsibility of databases is to ensure that all the transactions are performed successfully. Successful completion of transactions lead to a permanent change in the database as commits. In the case of failure at any point during the transaction, the database must revert to its original state, without any loss of data. This reverting back of states is known as rollback.

Another important thing to note about transactions are the permitted operations. A transaction can perform either read or write values in database tables. The queries performed on databases utilize these operations for analyzing and processing data. In short, databases need to be very careful with handling and processing their data, as any unexpected failures may hinder tasks that need to be performed.

Concurrency

In a database system, transactions are executed concurrently. This means that multiple transactions are executed at the same time.

As several transactions may access the same resource at the same time, concurrent transactions may cause inconsistencies. This is where data may get lost, or remain uncommitted.

Think in this way: A single transaction executes multiple queries, concurrent transactions would execute numerous queries at the same time! The database is sure to get confused with so many queries executing!

To deal with any possible concurrency issues, databases ensure they have the ACID properties:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

In this tutorial, we will focus only on the isolation property.

Read Phenomena

Concurrent transactions accessing the same resource at the same time can lead to read issues. To understand database isolation, we need to understand the different read phenomena which may occur during the execution of concurrent transactions.

Let's consider an example of the database of a clothing store. A sample table from the database with customer purchases is illustrated below.

Dirty Read

A dirty read is a reading issue that occurs when uncommitted data is read from the database.

Suppose transaction 1 wrote the last row in the database but did not commit it yet. At the same time, transaction 2 accesses the same resource and reads data from the table.

Since transaction 1 did not commit the changes it made yet, it will try to read uncommitted data. This is a problem, as if transaction 1 fails, the data may be rolled back, changing the values. This would lead to transaction 2 reading wrong values.

Non-repeatable Read

Non-repeatable read occurs when the same row is read twice from the database.

In our example, suppose transaction 1 updated a value in the database. As transaction 2 is running concurrently, it updates the same value in the database to some other value. When transaction 1 tries to access that value again, this value has changed from what this transaction wrote earlier.

Phantom Read

Phantom read refers to the problem where similar queries fetch different results from the same location in the database. The result may have changed during some time between the two queries.

Suppose transaction 1 ran a query on the database. Sometime later, concurrently running transaction 2 accessed the same resource and updated the table by adding new entries. When transaction 1 runs the same query again, it finds different output as compared to what it received earlier.

Now that we've got our concepts clear on the prerequisites, let's talk about isolation levels.

Isolation Levels

Isolation is the property of databases that ensures their integrity. Isolation levels are levels defined within a database system, which define how much a certain transaction needs to be isolated from any modifications made in the database by other transactions.

The SQL standard defines four isolation levels.

Read Uncommitted

The lowest isolation level allowed by SQL is read uncommitted. In this level, transactions can read data that is not committed by other transactions, permitting dirty read. It is safe to say that transactions are not isolated from each other in this isolation level.

Read Committed

This isolation level allows for the reading of data after it is committed by a transaction. This means no dirty reads are possible. This isolation is unable to prevent non-repeatable reads. This is ensured as locks are applied on the row being read. This prevents the reading, updating, and deletion of data.

Consider an example where a transaction needs to read twice, after some time gap. It is possible that another transaction comes and updates data after the first and before the second read of the initial transaction. The data read would be committed which prevents dirty read, but the values have changed.

Repeatable Read

This isolation tries to improve on the previous isolation level by preventing both dirty reads and non-repeatable reads. This prevention is done by applying locks on rows that are read and rows that perform write operations. However, this cannot prevent the addition of new entries to the database if the transaction is performing some work (not between two reads).

Serializable

The highest isolation level allowed is serializable. This isolation level looks like a serial execution, with almost no concurrency. All the three read phenomena are prevented in this isolation level, but it compromises concurrent execution.

Summary

Different isolation levels exist, and you can choose to use any one of them depending on your preferences regarding your database. A summary of isolation levels with the read phenomena they tackle is given below.

One Pager Cheat Sheet

• In this lesson, we will learn about database transactions and isolation levels, understanding the read phenomena and key points in database systems.
• SQL (Structured Query Language) is highly popular and is widely used among developers and companies to create, modify, and query relational databases in an efficient and intuitive manner.
• To perform operations on database tables, we execute a SQL query.
• Transactions commits or rollbacks performed reads and writes of values in the database, thus ensuring that any single operation is a collection of several smaller ones.
• Multiple transactions executing simultaneously can cause inconsistencies in the database, which can be prevented by the ACID properties, with a special emphasis on isolation.
• Concurrent transactions can lead to Dirty Read, Non-repeatable Read and Phantom Read issues when accessing the same resource at the same time.
• The results of a transaction are either committed permanently, or rolled back if unsuccessful.
• We are discussing Isolation Levels, which dictate how much a certain transaction needs to be isolated from other transactions in a SQL database system.
• Using varying isolation levels, you can choose to either allow or prevent Dirty Reads, Non-repeatable Reads, and Phantom Reads.