This post is to discuss the Isolation in database. It took me a little difficulty to understand this concept as I couldn't find any suitable example over internet. It was explained as a theoretical concept.
So, We'll try to discuss it using an example. But before that let's have a look on a statement on Isolation in my earlier post, "Any two or more transactions on a database are isolated from each other. i.e. they don’t interfere with each other."
Now let's take an example of table named EMP in a database which supports isolation. And we have two users: user X and user Y who frequently access/use this table.
The table contains data as shown in picture above.
In a situation, where user X logins into database and change the Age of Michelle from 19 to 23 as shown in picture below. And this change in the EMP table is uncommitted yet.
Now, consider three scenarios.
scenario(I): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user.
scenario(II): User X commits the change made by him on table EMP. After that User Y logins into database.
scenario(III): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user. After that user X commits the change.
Here is a task for you to guess how the data of table EMP would appear to user Y for each of three scenarios.
Here are the answers.
scenario(I): The data of table EMP would appear unchanged as in first picture to user Y.
scenario(II) and (III): The data of table EMP would appear changed as in the second picture.
Now, hopefully you would have figured out the reason why. Still, here is the explanation.
Only committed data is visible across the sessions. Since the data change was uncommitted for session(I), the last committed data of table EMP (as in first picture) would appear to user Y.
Now after going through this example, we conclude that any database changes/operations/transaction are isolated from each other across different sessions untill and unless those are committed. This is the property of isolation in the database.
Hope you liked my post. Here is more on this topic on Wikipedia
So, We'll try to discuss it using an example. But before that let's have a look on a statement on Isolation in my earlier post, "Any two or more transactions on a database are isolated from each other. i.e. they don’t interfere with each other."
Now let's take an example of table named EMP in a database which supports isolation. And we have two users: user X and user Y who frequently access/use this table.
The table contains data as shown in picture above.
In a situation, where user X logins into database and change the Age of Michelle from 19 to 23 as shown in picture below. And this change in the EMP table is uncommitted yet.
scenario(I): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user.
scenario(II): User X commits the change made by him on table EMP. After that User Y logins into database.
scenario(III): While the session of user X is still running with uncommitted data change, user Y logins into same database as a separate user. After that user X commits the change.
Here is a task for you to guess how the data of table EMP would appear to user Y for each of three scenarios.
Here are the answers.
scenario(I): The data of table EMP would appear unchanged as in first picture to user Y.
scenario(II) and (III): The data of table EMP would appear changed as in the second picture.
Now, hopefully you would have figured out the reason why. Still, here is the explanation.
Only committed data is visible across the sessions. Since the data change was uncommitted for session(I), the last committed data of table EMP (as in first picture) would appear to user Y.
Now after going through this example, we conclude that any database changes/operations/transaction are isolated from each other across different sessions untill and unless those are committed. This is the property of isolation in the database.
Hope you liked my post. Here is more on this topic on Wikipedia
No comments:
Post a Comment