Online College Courses for Credit

+
Isolation

Isolation

Rating:
(0)
Author: Sophia Tutorial
Description:

Recall how the isolation property ensures that the data used in a transaction cannot be used in a second transaction until the first transaction is completed.

(more)
See More
Tutorial

what's covered
This tutorial explores the isolation property in a transaction and how it affects the database in two parts:
  1. Introduction
  2. Isolation Example

1. Introduction

The isolation property in a transaction ensures that if there are multiple transactions that are run at the same time as each other that they do not leave the database in an inconsistent scenario. The transactions themselves should not interfere with one another and each of the transactions should be run independently. Any changes that are being modified in a transaction will only be visible in its own transaction but any other concurrent transaction will not see the results of the changes until the transaction is complete and the data has been committed. This also ensures that the transactions will that run concurrently will have the results the same as if they were run sequentially.

In addition, we cannot make the same operation with multiple transactions at the same time as the modified data should only be permitted to be changed by one transaction at a time. Most databases including PostgreSQL will use locking to maintain transactional isolation.

2. Isolation Example

Let us take our example where Jennifer would like to make a payment to Randall for $100 through an account transfer. This transaction would be a balance transfer between two accounts at two different branches at the same bank. Let us review what the transaction would look like:

  1. Jennifer’s (10) account would be deducted by $100.
  2. The banking location that Jennifer has her account would have their location’s account deducted by $100.
  3. The banking location that Randall(50) has his account would be increased by $100.
  4. Randall’s account would be increased by $100.

Let us look at the transaction in SQL:


BEGIN;

UPDATE customer_account 
SET balance = balance – 100 
WHERE account_id = 10;

UPDATE branch_account 
SET balance = balance – 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 10);

UPDATE branch_account 
SET balance = balance + 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 50);

UPDATE customer_account 
SET balance = balance +100 
WHERE account_id = 50;

COMMIT;

Let us say that Jennifer’s balance started at $1000 and Randall’s account started at $1000. If she was attempting to start this transaction and Randall concurrently was trying to check his account balance, if the transaction had not committed yet, Randall should not see any updates to his account until the changes are made. If he queries for his customer_account’s balance, it would be at $100 until the entire transaction from Jennifer executes successfully and commits the data to the database. In certain databases, if Randall tried to query his account’s balance, no result would be provided until Jennifer’s transaction was completed.

Isolation is important as you have more concurrent transactions that access the same data. For example, you could have a situation where Randall is getting two different account transfers at the same time from two different individuals. Say Randall is getting $100 from Jennifer and $50 from Paul.

If there was not isolation in place, Jennifer’s transaction could check Randall’s balance at $1000. At the same time, Paul’s transaction could check Randall’s balance at $1000. Jennifer’s transaction would add $100 to the $1000 and save the results. Paul’s transaction would add $50 to the $1000 and save the results. The final result is such that Randall’s account balance is $1050 instead of $1150. This is the case due to Jennifer and Paul both reading Randall’s balance at the same time before any changes were made. Then Jennifer’s transaction started and updated the balance but Paul’s transaction also completed and saved over Jennifer’s transaction. This is why isolation is important in a database as Jennifer’s transaction would prevent Paul from even reading the value. Even if the value is read, Paul’s transaction would not be able to complete due to the inconsistency in the database state.


summary
The isolation property ensures that multiple transactions can run concurrently without leading to the inconsistency of the database state.

Source: Authored by Vincent Tran