Howdy friends today we are going to discuss about concurrency control problems in DBMS. When databases work concurrently there are problems in concurrency control. In the real world transactions are not working individually. We know that. Let’s see what are those concurrency issues.
Those concurrency issues in database are dirty read, unrepeatable read problem, lost of update, phantom read, inconsistence analysis. Let’s learn one by one what these things are. In order to understand these operations it’s mandatory to know how read and write operations are perform in a database. So firstly we will learn that.
Now you know what are concurrency problems. Hope you know the answer for this question. what is control problem. But you might don’t know to explain them in detail. We will learn them.
Read/write operations in a database
Let’s think our operation is to find the value of variable a. So what the computer wants to do is go to the database and find the value of variable a. Then retrieve it and used it to the operations. Databases are stored in permeant storages. Therefore a variable values are stored in some address location. The meaning of finding a value of a variable in a database means finding the address location of that variable stored. Then read the value of that cell and retrieving it. Then after that using it for the future operations means computer has to store it in a location. That location is also a address location.
Read operation
Let’s say database need to perform read(a) operation. Firstly it will check in the buffer memory. Value of variable a whether it exists in the buffer memory. If exist it will read the value of buffer memory. If not it will do like this. So first it will read the address location where variable a then go to that location and read the value. After that in a buffer memory it will store the value of a. For future operations of database will refer value in the buffer memory.
Then we will see how write operation will perform. When a write operation is performing automatically read operation is happening if the value is not available in the buffer memory. After the operation was done. Let’s say operation is adding 10 to variable a. Then the computed value stores in the buffer memory. Earlier we learned about transaction states. If you don’t remember you can go through the post. Transaction states. When a transaction comes to the commit state all the values in the buffer memory will flush to the database. The database will update permanently.
Write operation
This is how read and write operations happens. Until transaction comes to the commit point changed values will be in the buffer memory. They are coming to the database once after transaction come to the commit point. Computer needs a value of a variable it will firstly look at the buffer memory. To see whether that variable is available in the buffer memory. If not it will loads the value to the buffer memory. Those are the important points you have to keep in your mind. If not you will completely misguided in understanding transaction theories.
Concurrency problems in database
Concurrency problem 01 – Dirty read problem in database
This problem happens when a one transaction was rolled back and other transaction is using value was modified by rolled back transaction. Let’s look at dirty read example.
| T1 | T2 |
| R(A) | |
| A = A + 10
W(A) |
|
| R(A) | |
| A = A + 5
W(A) |
|
| Commit |
Let’s assume variable A is 10. This is how this operations will happens.
| T1 | T2 |
| Reads the A value stores in buffer memory as A =10 | |
| Update the value in buffer memory as A= 20 | |
| Rollback | Since read operations checks whether variable A available in the buffer memory and read A = 20 |
| Update buffer memory A = 30 | |
| Update database permanently A = 30 |
When T1 rollback the changes done by T1 is no longer valid. But T2 used updated value of T1 and updated the database as well as. This is totally incorrect and leads to database inconsistency. We can call this type as a dirty read.
Concurrency problem 02 – Unrepeatable read problem
This issue is transaction read different values for the same variable in two different times.
| T1 | T2 |
| R(X) | |
| R(X) | |
| X = X + 10
W(X) |
|
| R(X) |
For instance Let’s assume X = 5
| T1 | T2 |
| T1 reads value of X and stored in buffer memory X = 5 | |
| T2 reads the value of X as 5 from buffer memory | |
| T1 update X = 15 in the buffer memory | |
| T2 reads the value of X as 15 from buffer memory |
In this example when X reads X value two time it gets two different value. T2 thinks it is the only transaction works with the database. So it’s confused. This kind of error we call as unrepeatable read problem.
Concurrency problem 03 – Lost of update
In this what happen is update value of one transaction is lost.
| T1 | T2 |
| R(A) | |
| A = A + 10 | |
| W(A) | |
| A = A + 5 | |
| W (A) | |
| Commit | |
| Commit |
Let’s assume value of A = 5. Let’s see the explanation.
| T1 | T2 |
| T1 reads the A = 5 and stores in buffer memory | |
| T1 update A = 15 in the buffer memory | |
| T2 reads A = 15 from buffer memory | |
| T2 update A = 20 and stores in buffer memory | |
| Update database A as 20 | |
| Update database A as 20 |
Final value in the buffer memory is A = 20. Therefore database will update as 20. T1 update A to 15. When it commits it update database A as 20 which means T1 update is not visible and it is lost. It looks like A is update from 5 to 20 directly and written in the database. We call this type of a thing as lost of update error.
Concurrency problem 04 – Phantom read
This problem is quite similar like unrepeatable problem. In unrepeatable problem the transaction reads two different values of the same variable. Here what happen is a transaction reads the value of a variable at the first time but in the second time it gets a error message that variable is not existing. This happen because another transaction has deleted the variable. Let’s see the example. Assume X as 2.
| T1 | T2 |
| R(X) | |
| R(X) | |
| Delete(X) | |
| R(X) |
| T1 | T2 |
| T1 reads X as 2 and store in buffer memory | |
| T2 reads X as 2 from buffer memory | |
| T1 delete variable X | |
| T2 get message X is not available |
Here T2 gets confused at the second reading because it reads the X = 2 at firstly but after few seconds for it’s secondly reading it gets X is not available. This occurs because another transaction T1 has deleted a variable needed for the T2 transaction execution.
Concurrency problem 05 – Inconsistency analysis
The inconsistency analysis issue occurs when aggregate value function is performing. When one function tries to get the cumulative value of some variable in the mean time if another variable change the value of those variables, this kind of issue will occur. Let’s see an example. Let’s Assume X = 2, Y = 4, Z = 5. We will see the example.
| T1 | T2 |
| Sum = 0 | |
| Read(X) | |
| sum = sum + X | |
| Read(Y) | |
| Write(Y) | |
| Read(Y) | |
| sum = sum + Y | |
| Read(Z) | |
| Z = Z +10 | |
| Read(Z) | |
| sum = sum + Z |
| T1 | T2 |
| Sum in the buffer memory is 0 | |
| Read X as 2 and store in the buffer memory | |
| update sum value as 2 | |
| Read Y as 4 and stores in the buffer memory | |
| Add 2 to the Y and update Y value in the buffer memory Y = 6 | |
| Read value Y = 6 from buffer memory | |
| Add the Y to the sum update sum in buffer memory as 8 | |
| Read value of Z as 5 and stores in buffer memory | |
| Update Z to the 15 and store in the buffer memory | |
| Read Z as 15 from buffer memory | |
| Add Z to the sum and update sum as 23 in the buffer memory |
In this example T1 is going to take sum of X,Y,Z. According to the values given T1 needs to get the sum as 11. Because sum = 2+4+5 = 11. Hence T1 gets the answer as 23 because while T1 performing the operation T2 has updated those values. This kind of problem we call as inconsistency analysis.
Conclusion
From this post we have discussed the concurrency problems. That is to say all about concurrency problems. In conclusion we are going to end this article. However if you have questions to get clarify you can comment and let us know. If you want to clarify about more topics please let us know by commenting. I hope this post will help you. Please check out other database posts as well as. Here is the post about database serializability. Goodbye! to all until we will meet with another post.
