ACID (Isolation)

Isolation could be used to solve problems in database synchronisation in multithreading.

Isolation means when running in a multithreaded environment, one transaction should be isolated from other transaction / non-transaction operations. And we can tune how strict this statement is true

There are 4 main isolation level. The higher level the more latency / slower our transaction speed will be

1. Read uncommited isolation level

This provide 0% isolation as you can read the uncommited data. Which will experience all the listed concurrency problem.

Note: postgres does not support this level of isolation and will be default to Read commited

2. Read commited isolation level

In this level, our transactional only read the commited operation, it will ignore all the uncommited operation.

For example the above, when Transaction 2 is trying to read Bal, it's blocked because Transaction 1 is still going on.

As a result, we can solve Dirty read — which happens because of uncommited data.

However we don't solve:

• Non-repeatable read
  • Reading only commited data does not guarantee that the data we later on query stays the same.
  • If the data that we're reading was changed and commited by another transaction, this will fail.
• Phantom read
  • Reading only commited data only apply for a row. Therefore when querying a range of data, another transaction still can change and commit additional data which could potentially affect our query.

How does it work?

When the transaction obtains Read lock for a row, the Read lock will also acquire a Write lock for the current row. Therefore other transaction needs to to for the Write lock to release until it can read it — until the current transaction is finished.

As a result, the other transaction cannot read or update the current row. — They have to wait for the current transaction to be completed

However, it will release both Read lock and Write lock as soon as it finished processing with that row. As a result, during these time, other transactions can still update the row. Later on when we read this role again, it could have a different value

[!note]
In mordern database, it does not blocked when the other transaction is working on a row. It created a snapshot of the current row before the transaction so that other transactions can read that snapshot instead.

3. Repeatable read isolation

In this one, we make sure that the value is not accessible for WRITE during the whole transaction block.

However we don't solve:

• Phantom read
  • Same problem, since the isolation only apply to the current row, it does not affect when we're dealing with a range.

How does it work?

When the transaction acquire a Read lock and Write lock for the current row, it only release the write lock when the transaction complete. As a result, other thread cannot write on this row.

4. Serialisation

We apply a Range lock for all the affected rows. Therefore no ones can touch this rows.

This will address Phantom read because now we apply to the whole range of data.