Concurrency control in a web application
I need to solve this situation: In my Spring + JPA web application, I have a jsp similar to an excel worksheet.
So, I have a certain number of cells, and each cell is stored in a DB table with additional information: I have a row for each cell.
id | value | column | row | ...
I am using this structure because the number of columns in my jsp table is dynamic.
At the moment, when I save the cells , I am trimming the current set of rows in the DB table and re-inserting all new rows . This is the fastest way to find a large set of rows.
But now I have a concurrency issue: the jsp page can be used by different users at the same time and this can cause rewrite issues while saving other users .
I need to implement some kind of lock in my web application. I found that there are basically two types of blocking: optimistic and pessimistic.
Can you give me a general approach to solving this situation? Where do I need to implement locking, data access layer or service layer?
NOTE to be more clear: table values are shared between users, but can be updated by any of the authorized users.
source to share
The decision is likely to depend on the behavior requirements.
How about the following scenario: Users A and B started changing some values, then User A clicked the Save button and saved the data after User B did the same. User B received an error message: "data has been updated, please reload the page." He reloads the page and loses all the changes he made: (Only then can he save his changes, but he has to do it again.
Another possible scenario: Users A and B access the page, but only the user who was the first can save their work, other users will see a message that says something like "someone else is editing the page, please try again later" ...
For the first scenario, you can implement the following: each row in the table (in the database) has a last-date-update-timestamp, which is updated to the current time every time that row is changed. Now imagine that user A gets a timestamped string 1
when opening the page, user B was a little slower and got the same timestamped string 2
. But he made his changes faster and hit the "Save" button. Now the string is saved to the DB with a timestamp, say 5
. User A tries to save his changes, but his data label 1
is different from 5
the one currently in the DB. This means that someone has already changed this data and should see the error message I mentioned above.
The second scenario is a little more difficult to implement. I think the best way to do this is to open a transaction for the DB which
- reads the lines we need;
- put the "blocked" flag on
true
for all of them: - if some row is already locked, crash (or return available rows, whichever you want). But probably should fail;
- returns strings to jsp page;
Now, if another user has requested the same rows, the transaction will fail and he will not be able to start modifying the data.
User A should put these flags locked
back in false
when he saves the data.
An important thing . These locks must time out to prevent a situation where the user opened the page and closed it without saving (or crashing in the browser or whatever). You may also want to implement some kind of reackquire lock for the same user - when the user first opened the page and then closed it without saving the data and reopening it - he should be able to edit the data. This can be done by identifying the user somehow - login, cookie, etc.
source to share