Sundarrajk's Weblog

How to avoid concurrent edits to same Record without locking at the Database level?
Every table which could have a concurrency problem should have a column called rowstate. This column must have a value 1 when the record is inserted.



Here is how the RowState column will be used for resolving conflicts that could arise due to concurrency. Consider the scenario where two users are trying to modify the same record. The following will be the flow:
1. User A requests for the Record.
2. The Record is sent to User A with the RowState value reading “1”.
3. User B requests for the Record.
4. The Record is sent to the User B with the RowState value reading “1”.
5. Now both users are viewing data which are replica of each other.
6. Now say User B makes changes and submits the changes.
7. The record is updated in the database with the new values as specified by the user and the RowState value is updated to “2”.
8. Now User A submits the changes. The database will now not be updated as the RowState that we will get from User A will be “1” whereas the value of the RowState in the database is “2”. The User A will now be prompted that the data has changed between the time the user viewed it and the time the user tried to save it. The user will be shown the new values (as modified by User 2) and will be asked to redo the changes.
This way we will prevent concurrent users from inadvertently overriding each other’s changes.
In relational world, this is generally referred to as optmistic locking

An example …
An employee gets a pay-hike of 20% for his excellent performance and also a hike of
5% based on the performance of the company. Employee salary record can be updated
in HRMS. Local-HR wants to update the employee-salary with 20% hike and Corporate-HR
wants to update the employee-salary with 5% hike.
Both Local-HRManager and Corporate-HRManager open employee-salary update screen
at the same time. If the existing salary is Rs.10,000, both have read it as Rs.10,000.
Local-HRManager updates it to Rs. 12,000 (20% hiked). Corporate-HRManager updates it
to Rs. 10,500 (5% hike). Since the second update has been done by Corporate-HRManager,
effectively employee got a hike of Rs.500 which by no means is acceptable 🙂
Logically, if the employee salary record needs to be updated in the order of local
followed by corporate, then the updated employee salary should have been Rs.12,600.
With optimistic-locking, when Corporate-HRManager tries to update the salary record,
the record-version he/she has read does not match with the current record-version
in the salary table. Hence the transaction would be canceled asking the Corporate-HRManager
to “Retry after fetching the details once again”.