Isolation level

I have learned about isolation levels in database, so I just want to write down a simple post to remember it better.

1. Read phenomena

When transaction A reads the data that might be change by transaction B.

1.1 Dirty reads:

Is when a transaction read uncommitted data from another transaction. Example:

Transaction A

Transaction B

begin transaction;

select age from employee where id = 1;

// age = 24

begin transaction;

update employee set age = 25 where id = 1;

// age = 25

select age from employee where id = 1;

// age = 25

rollback;

select age from employee where id = 1;

// age = 24

commit;

1.2 Non-repeatable reads

When during a transaction, you retrieves a row two times, and in the second time, you got a slightly different row. It different with dirty read that this time, it read committed data. 

Transaction A

Transaction B

begin transaction;

select age from employee where id = 1;

// age = 24

begin transaction;

update employee set age = 25 where id = 1;

// age = 25

commit;

select age from employee where id = 1;

// age = 25

commit;

1.3 Phantom read

When during a transaction, you performs two query and the number of rows you got each time is different due to some other transaction insert or delete new data.

Transaction A

Transaction B

begin transaction;

select * from employee where age > 18 and age < 24;

// 4 rows

begin transaction;

insert into employee(id, age) values(20);

commit;

select * from employee where age > 18 and age < 24;

// 5 rows

commit;

2. Isolation level

2.1 Read uncommitted

Transaction A could see uncommitted changes from Transaction B, in other words, it allow dirty read to happen.

2.2 Read committed

Transaction A could see committed changes from Transaction B. No dirty read at this level, but non-repeatable read and phantom read are possible.

2.3 Repeatable reads

It inherits from read committed, and no non-repeatable read in this level, it means that no matter how many time you query for a row in a single transaction, you are warranty that all the values in the row remain unchanged.

But phantom read could happen at this level.

2.4 Serializable

Not any read phenomena could happen at this level, this is the highest level of isolation.

In Serializable Isolation Level, all transactions have to execute in sequential order, it cannot be execute in parallel like in Repeatable level.

3. Default Isolation level in Postgres

Default isolation level of Postgres is Read Committed.

There are no way to read uncommitted in Postgres.

Phantom read are prevent even in Repeatable reads Isolation Level.

4. References

https://en.wikipedia.org/wiki/Isolation_(database_systems)