System Design#A3 : Transaction levels
Transaction isolation levels
When two transaction occurs in a distributed system consistency can arise
So what read guarantees we are given?
Read Uncommitted Isolation level
Here T1 and T3 belongs to transaction 1 and T2 and T4 belongs to transaction 2.
In the above image when t1 is executed the value of id 2 will become 40 in an uncommitted fashion which means if this transaction fails then rollback will be performed and value will become 70 again let consider here the transaction never fails.
So after t1 ,t2 gets executed t2 will read id 2 value = 40 which is uncommitted hence not safe.
Now let’s say at execution of t3 transaction 1 fails now system is rolled back with previous value of id 2 which is 70 but t2 has already read it as 40 hence t2 is reading a wrong value hence a consistency issue.
Read Committed Isolation Level
First r1 is executed the difference here is that after performing the write 2,40 operation, the changes are not committed into the original database . A dummy table for that particular transaction is created and dummy table will not contain the full database it will only contain the change data which is to be committed in future in the main database. Also no. Of dummy tables will be equal to number of transactions
So when R2 gets executed, and execute the read 2 command, the value which will be extracted will be value from the original database table. So answer for this operation will be 80.
Repeatable Read Isolation Level
We need to get same value if we read multiple times a same key in a particular transaction .
In repeatable read the process remains the same as read committed the only difference which is generated is that when transaction 1 is executed a full snapshot of database is created for that transaction same goes for transaction 2 so number of new snapshot will be equal to number of transactions.
So here, every change occur in the local copy and the read operation also gets happened from the local copy The interaction with the main copy is occurred, only at commit Time.
Serializable Isolation Level
Highest level of isolation.
It is done by executing transaction in a serial manner
In serialisable isolation level, no two transactions can interfere with one another If the first transaction want to change the database, it will first put a lock on the database so that the second transaction can’t interfere with the database.
We should use the serialisable technique if we don’t want to encounter phantoms
What are Phantoms?
Imagine we are not using a serialisable Isolation level and there are two transactions, T1 and T2
T1 transaction is
Begin
Read Sum(all)
Read Sum(all)
T2 transaction is
Begin
Read 1
Write 2,40
Read 1
Write 2,30
Commit
Main database is
1 : 10
2 : 20
Where one and two are keys and 10 and 20 are values
In this transaction, let’s say the transactions are not locking the database hence they are not executing in a serialisable isolation fashion. So here for each transaction, local copy of database will be created.
Let’s say the whole process executed in this fashion
First transaction 1 to read sum(all) is executed which gives result 30. Now the whole transaction 2 gets executed. So the key 2 will become 30 in the main database.
With the rest of the transaction one get executed The read sum(all) will give 40 .So for the same sum(all) we are getting to 2 results. This is called the phantom problem and this problem can be solved by serialisable isolation transaction. Try to solve this problem by using serialisable isolation transaction.
