I explain using memes

Introduction

In the world of databases, security and reliability are fundamental aspects on which effective data management is built. One of the key components of security in the context of transactional operations is the level of transaction isolation.

In this article, I want to tell and show with clear examples: what insulation is, what they are needed for, and how they can be used.

Whether you’re new to databases or an experienced developer looking to deepen your knowledge, this article offers useful information and practical tips. Let’s begin our exciting journey into the world of transaction isolation levels to gain confidence and proficiency in working with this key component of database management systems.

Diving into theory

First, let’s talk about acid (ACID)

ACID states that while a transaction is executing, other transactions must not influence the outcome. However, providing complete transaction isolation can be costly and have several unpleasant consequences, including:

1. Lost Update: When two concurrent transactions modify the same data, uncertainty arises in the resulting state of the data. The result of the update may be unpredictable because both transactions conflict and may overwrite each other’s changes.

2. Dirty read: A transaction may produce intermediate query results from concurrent transactions that have not yet completed. This may result in inconsistent results and incorrect display of data.

3. Non-repeated read: If queries with the same conditions are executed within the same transaction, the results of these queries may differ. This occurs because other concurrent transactions have already made changes to the data between query executions, causing inconsistent results.

4. Phantom reading: By repeatedly executing the same query within a transaction, rows of data that have been modified by concurrent transactions may appear and disappear. This gives the impression that some lines “appear out of nowhere” or “magically disappear.”

All of these undesirable consequences arise when using transaction isolation levels that do not fully guarantee absolute isolation. In the following sections, we’ll look at the different transaction isolation levels in PostgreSQL and how they impact these aspects, and provide practical examples to help you better understand and choose the most appropriate isolation level for your needs.

There are 4 levels of transaction isolation that eliminate the disadvantages associated with parallel execution of transactions. Let’s look at them in order of increasing isolation level:

1. READ_UNCOMMITTED : (Read Uncommitted Data): Dirty reads, non-repeating reads, phantom reads, and orphaned update can occur in this level. Here, transactions are not completely isolated from each other, so they can see uncommitted changes to other transactions.

2. READ_COMMITTED: Dirty reads are prevented at this level, but non-repeated reads, phantom reads, and orphaned updates may occur. Transactions only see committed changes from other transactions.

3. REPEATABLE_READ : Dirty reads, non-repeated reads, and orphaned update are prevented at this level, but phantom reads may occur. The results of queries within a transaction remain constant even in the presence of changes made by other transactions.

4. SERIALIZABLE: At this level, transactions are completely isolated from each other. The influence of one transaction on another during execution is eliminated. This level prevents all unwanted effects associated with parallel execution of transactions.

Each of these isolation levels has its own characteristics and is applicable in different scenarios. In the following sections of our article, we will take a closer look at READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE as we will operate with Postgresql.

In PostgreSQL, you can request any of the four transaction isolation levels, but only three different levels are implemented internally, meaning the Read Uncommitted mode in PostgreSQL acts like Read Committed. The reason for this is that this is the only way to map standard isolation levels to PostgreSQL’s multi-version concurrency architecture.

Also in the Postgres documentation there is the following table describing how different isolation levels solve the unpleasant consequences described earlier:

In my opinion, information is remembered much better when it is supported by an established and memorable visual sequence, so let’s slightly change the format of this table and go through it again, designating each type of isolation with its own character, whose level of coolness will directly depend on the level of isolation (this is by no means does not reduce the importance of each level; each of them is indispensable in its business case):

Read Committed: Let’s say you have transaction A that starts reading data from the orders table. Transaction B then makes changes to the orders table and commits them.

• At the Read Committed isolation level, transaction A will not see changes made by transaction B because those changes have not yet been committed. Transaction A will only see committed data that was available when it started its transaction.

Repeatable Read: Let’s say you have transaction A that starts reading data from the products table. At the same time, transaction B adds a new product to this table.

• When using the Repeatable Read isolation level, transaction A will see the data as if it were committed when transaction A started. That is, even if transaction B added new data, transaction A will not see that data because it is focusing on a snapshot of the data , made at the start.

Serializable: Let’s say you have two transactions A and B that simultaneously try to change the account balance by a certain amount. Transaction A begins its operation before transaction B begins its operation.

• The Serializable isolation level provides sequential transaction isolation. If Transaction A has already begun updating the account balance, then Transaction B will not be able to begin its operation until Transaction A has completed. This prevents conflicts and ensures strict data integrity.

Tips for use

Let’s look at some specific business examples where each transaction isolation level can be useful.

1. Read Committed: Let’s say you have an online store where users can place orders. When you use the Read Committed isolation level, each transaction will only see committed data, which will ensure order integrity and prevent you from viewing uncommitted changes to other orders.

2. Repeatable Read: Imagine you have an online bank where users can make transfers between their accounts. Using the Repeatable Read isolation level ensures that each transfer is based on a consistent snapshot of data, and prevents the possibility of phantom transfers or unexpected account balance changes between transactions.

3. Serializable: Let you have a warehouse management system where different employees can add or change inventory records at the same time. Using the Serializable isolation level will ensure strong data integrity and minimal conflicts when accessing warehouse records simultaneously.

It is important to analyze your specific business requirements and weigh data integrity, performance, and concurrency needs when choosing the appropriate transaction isolation level.

Remember, the higher the isolation level, the higher the performance cost of the operation

Examples

Read Committed:

-- Транзакция A
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM orders WHERE status="pending";
-- В другой параллельной транзакции будет внесено изменение в статус заказа
SELECT * FROM orders WHERE status="pending";
COMMIT;

-- Транзакция B
BEGIN TRANSACTION;
-- Изменение статуса заказа в параллельной транзакции
UPDATE orders SET status="fulfilled" WHERE order_id = 123;
COMMIT;

-- Транзакция A видит только зафиксированные изменения и не учитывает изменения, сделанные транзакцией B, до фиксации.

Repeatable Read:

-- Транзакция A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products;
-- В другой параллельной транзакции будет добавлен новый товар
SELECT * FROM products;
COMMIT;

-- Транзакция B
BEGIN TRANSACTION;
-- Добавление нового товара в параллельной транзакции
INSERT INTO products (product_id, name) VALUES (1001, 'New Product');
COMMIT;

-- Транзакция A видит только данные, как если бы они были зафиксированы в момент начала транзакции A, и не учитывает новый добавленный товар.

Serializable:

-- Транзакция A
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;

-- Транзакция B
BEGIN TRANSACTION;
-- Попытка выполнить ту же операцию, что и транзакция A
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
COMMIT;

-- В результате использования уровня изоляции Serializable возникнет блокировка, и транзакция B будет ожидать завершения транзакции A, чтобы избежать конфликтов и обеспечить последовательность выполнения операций.

Conclusion

In conclusion, it can be emphasized that a thorough understanding of transaction isolation levels is essential when developing and designing databases.

The choice of isolation level should be based on the specifics of your application and data integrity requirements. A higher isolation level can provide greater data reliability, but can also result in lower performance due to the occurrence of locks and waits.

Remember that choosing the right isolation level should be a thoughtful decision that takes into account your application requirements and business logic.

Thank you for reading to the end! Please leave your comments, suggestions and objections – it will be a great experience for me!