Understanding Isolation in Database

Isolation in database refers to the ability of a database system to allow multiple transactions to access the same data without interfering with each other. Isolation ensures that each transaction sees a consistent view of the data, regardless of the concurrent activities of other transactions.

Types of Read Phenomena

There are three main types of read phenomena

Dirty reads.

Non-repeatable reads.

Phantom reads.

Dirty Reads

Dirty reads occur when a transaction reads data that has been modified by another transaction but not yet committed. This means that the transaction is reading data that is still in an intermediate or "dirty" state, and it may be rolled back later.

To illustrate this, let's consider a simple example. Suppose we have a table named "employees" with the following columns: "id", "name", "salary", and "department". Transaction A executes the following query:

SELECT salary FROM employees WHERE id = 1;        

Meanwhile, Transaction B updates the salary of employee with id 1 using the following query:

UPDATE employees SET salary = 60000 WHERE id = 1;        

However, Transaction B does not commit the update yet. If we are using Read Uncommitted isolation level, Transaction A can read the new value of the salary column even though Transaction B has not committed the update yet.

To demonstrate this, let's run the following SQL queries:

-- Start Transaction A
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT salary FROM employees WHERE id = 1;
-- End Transaction A

-- Start Transaction B
BEGIN TRANSACTION;
UPDATE employees SET salary = 60000 WHERE id = 1;
-- End Transaction B        

If we are using Read Uncommitted isolation level, the read operation in Transaction A may return the new value of the salary column even though Transaction B has not committed the update yet.

To prevent dirty reads, we can use at least the Read Committed isolation level, which ensures that a transaction reads only committed data.

In SQL, we can set the isolation level for a transaction using the SET TRANSACTION statement. To set the isolation level to Read Committed, we would use the following query:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;        

In conclusion, dirty reads can occur in database systems when a transaction reads data that has been modified by another transaction but not yet committed. To prevent dirty reads, we can use at least the Read Committed isolation level, which ensures that a transaction reads only committed data.

Non-Repeatable Reads

Non-repeatable reads occur when a transaction reads a row twice but gets different values in each read. This can happen when another transaction modifies the row in between the two reads.

To illustrate this, let's consider a simple example. Suppose we have a table named "employees" with the following columns: "id", "name", "salary", and "department". Transaction A executes the following query:

SELECT salary FROM employees WHERE id = 1;        

Meanwhile, Transaction B updates the salary of employee with id 1 using the following query:

UPDATE employees SET salary = 60000 WHERE id = 1;        

If we are using Read Committed isolation level, Transaction A will not see the new value of the salary column until Transaction B commits. However, if we use Repeatable Read isolation level, Transaction A may see the new value of the salary column on its subsequent reads.

To demonstrate this, let's run the following SQL queries:

-- Start Transaction A
BEGIN TRANSACTION;
SELECT salary FROM employees WHERE id = 1;
-- End Transaction A

-- Start Transaction B
BEGIN TRANSACTION;
UPDATE employees SET salary = 60000 WHERE id = 1;
COMMIT;
-- End Transaction B

-- Start Transaction A again
BEGIN TRANSACTION;
SELECT salary FROM employees WHERE id = 1;
-- End Transaction A again        

If we are using Repeatable Read isolation level, the second read operation in Transaction A may return a different value for the salary column compared to the first read operation, since the row was updated by Transaction B.

To prevent non-repeatable reads, we can use Serializable isolation level, which ensures that a transaction sees a consistent snapshot of the database throughout its execution, even if other transactions modify the same data.

In SQL, we can set the isolation level for a transaction using the SET TRANSACTION statement. To set the isolation level to Serializable, we would use the following query:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;        

In conclusion, non-repeatable reads can occur in database systems when a transaction reads a row twice but gets different values in each read, due to another transaction modifying the row in between. To prevent non-repeatable reads, we can use Serializable isolation level, which ensures that a transaction sees a consistent snapshot of the database throughout its execution.

Phantom Reads

Phantom reads refer to the situation where a transaction reads a set of rows that satisfy a certain condition, but when the same transaction repeats the same read operation later, additional rows appear that were not visible before. This happens when another transaction commits a new row that satisfies the same condition.

To understand phantom reads better, let's look at an example:

Suppose we have a table named "employees" with the following columns: "id", "name", "salary", and "department". Let's say Transaction A executes the following query:

SELECT * FROM employees WHERE department = 'Sales';        

Meanwhile, Transaction B inserts a new row into the employees table with department 'Sales' using the following query:

INSERT INTO employees (name, salary, department) VALUES ('John Doe', 50000, 'Sales');        

If we are using Read Committed isolation level, Transaction A will not see the new row inserted by Transaction B until Transaction B commits. However, if we use Repeatable Read isolation level, Transaction A may see the new row inserted by Transaction B on its subsequent reads.

To demonstrate this, let's run the following SQL queries:

-- Start Transaction A
BEGIN TRANSACTION;
SELECT * FROM employees WHERE department = 'Sales';
-- End Transaction A

-- Start Transaction B
BEGIN TRANSACTION;
INSERT INTO employees (name, salary, department) VALUES ('John Doe', 50000, 'Sales');
COMMIT;
-- End Transaction B

-- Start Transaction A again
BEGIN TRANSACTION;
SELECT * FROM employees WHERE department = 'Sales';
-- End Transaction A again        

If we are using Repeatable Read isolation level, the second read operation in Transaction A may return a new row inserted by Transaction B. This is because Repeatable Read isolation level uses a snapshot of the database taken at the beginning of the transaction, and any subsequent changes made by other transactions are not visible to the current transaction.

To prevent phantom reads, we can use Serializable isolation level, which ensures that a transaction sees a consistent snapshot of the database throughout its execution, even if other transactions modify the same data.

In SQL, we can set the isolation level for a transaction using the SET TRANSACTION statement. To set the isolation level to Serializable, we would use the following query:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;        

In conclusion, phantom reads can occur in database systems when a transaction reads a set of rows that satisfy a certain condition, but additional rows appear when the same transaction repeats the same read operation later. To prevent phantom reads, we can use Serializable isolation level, which ensures that a transaction sees a consistent snapshot of the database throughout its execution.

If you enjoyed reading this article, please subscribe to it and tell your peers about it.