Consider the following application functions and then describe appropriate concu
ID: 3764968 • Letter: C
Question
Consider the following application functions and then describe appropriate concurrency control implementation in Oracle accordingly. Your description should address the following items: isolation levels, locks (type, level, long duration), and exception check (deadlock, write error, etc.). The following function is an example. A customer books a seat on a flight. The application checks the number of passengers on the flight. If the flight has reached its capacity, then the transaction will fail. A journalist retrieves an article from the database by specifying an article id. The journalist can then edit the article and then save it back to the database. Assume that an article may have multiple authors. A user transfers money from account x to account y. Consider the COMPANY database used in the lectures. A data entry clerk enters new employee information for a department. The application checks the number of employees in the department. If the number of employees of the department exceeds a threshold, the new employee should be rejected. Assume that this is for the largest company in the world and there are tens of thousands of clerks entering employee information simultaneously.Explanation / Answer
A)
Whenever there is the situation of reading and writing the data to the particular file system or into the database,
there should be the lock mechanism implemented to control the concurrent execution of the data.
As per told in the question that there are all total of 16 authors which can access the file on which
a particular user is already doing some work(read/write) on it.
The solution of the problem to prevent the data inconsistency is to take a lock whenever the user want to
read/write the data to the file. If this is in the process, if in case second user tries to access that particular file
then the file will be already locked by the previous user,hence the user will not be able to do any modification.
Exception needs to be thrown when the user tries to access the file which is already locked.
Deadlock also needs to be checked because it is possible that the user A is trying to get the resource which resource B has.
Thus, to prevent the data inconsistency, incomplete data, deadlock and the exception all of these check needs to be done.
B)
This is the case of the Bank problem which involves the most issues related to the concurrent access.
Now, we have a user who transfer amount from account X to account Y.
To have the proper balance information related to the account, the access to each of the account should be atomic,
i.e only one user is able to do the operation at the time.
Let us assume the sitiation that we have a user that transfer amount 500 from account X to account Y.
User of the account X knows that there is balance of 500 in it.User is not aware that some amount is being transferred from account X to account Y.Transfer is done and at the same time user recieves the message of insufficient balance.
Hence this kind of the situation should be dealt with the locks.
When any user is doing any transaction on the database other user should not be allowed to modify or change the data, or any operation that allows another user to change the data.
All the operation should be completely atomic which leads to the consistent data.
C)
This problem is the same as discussed in the example in the question i.e booking a seat on the flight.
The application should write lock the entry whenever the clerk is trying to enter the new data.
Before entering the data, there should be check whether the number of the employees have exceeded.
If not, then only the data entry clerk should proceed, along with acquiring the lock.
After the lock have been acquired the other clerk will not be able to input the new data.
If the capacity is reached then the action should be cancelled.
No check for the deadlock is needed, only the locking is required.