Transaction Isolation in DBMS

Suppose we are executing two transactions T1 and t2 to update student Rose’s last name. T1 updates last name to ‘M’ while T2 updates it to ‘Mathew’. Suppose these two transactions are executed concurrently, and T1 starts first and each step of transaction is viewed as below. Though both transactions are concurrent, each step in them takes very minute fraction of seconds to execute and below snapshot shows those steps. This shows how they affect the result of the transaction at those minute intervals on concurrency. Here what will be the final result? Last_name is updated to ‘Mathew’. What happens to T1’s update? It is lost!

Suppose, T2 reads the data after T1 updates the name but before commit. Again here T2 will execute as if it is unaware of T1’s update. Again last_name would be ‘Mathew’. Suppose T2 is allowed to read T1’s uncommitted update. Hence T2 will read last_name as ‘M’ and update it. Again last name is ‘Mathew’. But what happens to T1’s update? It is lost. It is no where saved and no will have record of its status!

Imagine the update is something like increment in salary by 10% with below steps and T2 is allowed to read uncommitted update by T1. What happens to salary? It will be incremented twice – once by T1 and secondly by T2 which increments the salary updated by T1. This will lead to incorrect data.

Hence it is very much important to execute concurrent transaction with utmost care, so that it gives consistent result. Transactions should be executed in such a way that its result should not affect other transactions; similarly current transaction should not be affected by other transactions. In this way we can achieve isolation of transaction and hence consistency of DB.

Translate »