TransactionIsolationLevel.md

Transaction isolation level

• A database transation must satisfy the ACID property

ACID

ACID	Description
ATOMICITY	Either all operations complete succesfully or the transaction fails and the db is unchanged
CONSISTENCY	The DB state must be valid after the transaction. All constraints must be satisfied
ISOLATION	Concurrent transactions must not affect each other
DURABILITY	Data written by a successful transaction must be recorded in persistent storage

Read Phenomena

Occurs when there is interference with transactions. If a DB is running at a low level of transaction isolation

• Dirty Read
• Non-repeatable read
• Phantom Read
• Serialization Anomaly

Dirty Read

A transaction reads data written by other concurrent uncommitted transaction

Non-repeatable Read

A transaction reads the same row twice and sees different value because it has been modified by other committed transaction

Phantom Read

A transaction re-executes a query to find rows that satisfy a condition and sees a different set of rows due to changes by other committed transaction

Serialization anomaly

The result of a group of concurrent committed transactions is impossible to achieve if we try to run them sequentially in any order without overlapping

How to deal with this phenomena?

The ANSI (American National Standards Institute) defined:

4 Standard isolation levels

name	level	desc
Read Uncommitted	low	Can see data written by uncommited transaction. Allows dirty read to happen
Read Commited	med-low	Only see data written by committed transaction. Dirty read does not happen
Repeatable Read	med- high	Same read query always returns same result
Serializable	high	Can achieve same result if execute transactions serially in some order instead of concurrently

Get current transaction isolation level

MySQL

Get Isolation Level

-- Get current session isolation level
SELECT @@transaction_isolation;

-- Get current global isolation level
SELECT @@global.transaction_isolation;

Change Isolation level in mysql

set session transaction isolation level read uncommited;

Showing the read phenomena

Using Read Uncommitted isolation level - Dirty Read

Using Read Committed isolation level - Dirty Read

Using Read Committed isolation level - No repeatable read

Using Read Committed isolation level - Phantom read

Using Repeatable Read isolation level - Repeatable and Phantom prevented

Postgres Using Repeatable Read isolation level - Repeatable and Phantom prevented

Understanding a serialization anomaly

In the image below the second record should be 540 = 270 + 270

Using serializable level

In the image below the sum is not allowed but postgres gives us a HINT: The transaction might succeed if retried

Summary

Keep in mind:

• Implement Retry Mechanism: There might be erros, timeout or deadlock.
• Read documentation: Each DB engine might implment isolation level differently

PostGres

MySQL

Comparison