There are some concepts that I have learnt in school but can’t seem to remember, revisiting wikipedia every few years. For example, database isolation levels. I wrote this simple post to help me reason and remember.

There are four isolation levels, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READS, and SERIALIZABLE.

Imagine A and B are transactions.

Read uncommitted

• A: select salary from employees where employee_id=5; // 1000
• B: update employees set salary = 2000 where employee_id=5; //salary updated to 2000
• A: select salary from employees where employee_id=5; // 2000

Read committed

• A: select salary from employees where employee_id=5; // 1000
• B: update employees set salary = 2000 where employee_id=5; //salary updated to 2000
• A: select salary from employees where employee_id=5; // 1000
• B: * commit transaction *
• A: select salary from employees where employee_id=5; // 2000

Repeatable reads

• A: select salary from employees where employee_id=5; // 1000
• B: update employees set salary = 2000 where employee_id=5; //salary updated to 2000
• A: select salary from employees where employee_id=5; // 1000
• B: * commit transaction *
• A: select salary from employees where employee_id=5; // 1000

Serializable

• A: select count(*) from employees; // 1
• A locks selected records
• B needs to wait until A releases lock
• A: select count(*) from employees; // 1