Transactional Isolation levels
What happens when two transactions trying to update the same data parallely?
This is handled by different transactional isolation levels present.
They are:
- Read Committed.
- Repeatable Read
- Serializable
- Read Uncommitted.
Read Committed: This is default isolation in Postgres, Here select query only only sees:
-> Data that was committed before query began.(query and not the transaction).
-> Doesn't see uncommitted changes.
-> Only sees changes done by itself and not other transactions.
Note : This all applies when change or update is done by 2 or more transactions on the same row.
Update, Delete in Read Committed can update rows that are already being updated by another concurrent transaction. In this case transaction will wait for previous updating transaction to either commit or rollback (if still in progress).
=> If first transaction commits, second transaction fails to update the record and if first one rollbacks, then second one can update the record.
Repeatable Read: Here isolation is at transactional level and not at query level. The only difference between Repeatable Read and Read Committed is that Repeatable Read throws error: could not serialze access dur to concurrent update, while in Read Committed it says Update 0 and this will only happen when out of 2 transactions updating the same row and one of then gets committed.
This is because repeatable read cannot modify rows changed by other transactions after it has begun execution.
Difference between Read Committed and Repeatable Read
In Read Committed, if select query is performed, then it may be possible that result each time is different sunce it works at query level.
In Repeatable Read, fix result for select query since it works at transactional level.
Read Committed is best for OLTP transactions. Repeatable Read provides consistent view of data even while it is being modified.
Serializable Isolation: Highest and most strict form of isolation. This works by using locks (read and write locks) and release it by the end of the transaction.
Range locks are used where select query uses a range especially to avoid phantom reads. Here transactions are committed as if they were running serially one after another and not concurrently.
Read Uncommitted: Lowest level of isolation. Here dirty reads can occur as transaction can read non committed changes by another transaction.
That's all for now, for the db isolation levels on the transaction.
