SQL vs NOSQL — Part II — Isolation Basics

Isolation enables concurrent execution of transactions, which helps in

1. Increased throughput
2. Better utilisation of CPU
3. Increased response time

These are the problems that can occur during the concurrent executions of transactions that can result in data inconsistency.

1. WR- Write Read conflict /dirty read
2. RW- Read Write conflict /unrepeatable read
3. WW- Write Write conflict /lost update or blind write

To overcome these above mentioned issues, databases provide different isolation levels as mentioned below. Weak to Strong (1–>4)isolation levels with increasing overhead, increasing consistency guarantees and decreasing concurrency.

1. READ UNCOMMITTED,
2. READ COMMITTED,
3. REPEATABLE READ,
4. SERIALIZABLE.

Keep in mind that each transaction can be executed in different isolation levels.

Also we got to know whats Buffer pool.

Buffer pool — When read transaction is executed, the data is read from the disk into the resident memory(Buffer pool)of the system and when the transaction writes a data it is not directly written to the disk, it only writes the data in to the Buffer pool/memory and not into the disk. The data will be written to disk only when commit statement is executed, obviously for performance reasons.

Write Read conflict / Dirty reads

In the below example, transactions T1 & T2 are interleaved.

Example: T1 is concurrent with T2

T1 ->Update employee set salary = salary + 1000$ where id=1006;

T2 ->Select Avg(salary) from employee;

1. T1 increments the salary.
2. T2 includes the incremented salary of the T1 while computing the average.
3. If T1 fails to commit, due to failure and the transaction is aborted. T2 reads the incremented salary which is never persisted in the disk(dirty read)

Reading the uncommitted data from the buffer is known as dirty read.

Transactions are allowed to do the dirty reads when the isolation level is set to READ UNCOMMITTED.

When transactions are set to READ COMMITTED, it will always reads the committed data. Dirty reads are not allowed.In the above example , T2 is always guaranteed to read the committed data.

Next blog — .RW- Read Write conflict aka unrepeatable read.