3

I have to solve this situation: in my Spring + JPA web application I have a jsp similar to an excel work sheet.

So I have a certain number of cells and each cell is saved in a DB table with additional information: I have a row for each cell.

id | value | column | row | ...

I use this structure because number of columns in my jsp table is dynamic.

At the moment, when I save cells I truncate the current set of rows in DB table and re-insert all the new rows. This is the fastest way I found to update a large set of rows.

But now I have a concurrency problem: the jsp page can be used by different users at the same time and this can cause overwriting problems on other users savings.

I need to implement some kind of lock in my web app. I found there are mainly two types of lock: optimistic vs pessimistic.

Can you suggest me a common approach to solve this situation? Where do I need to implement the lock, at data access level or at service level?

NOTE to be more clear: table values are shared among users, but can be updated by anyone among authorized users.

davioooh
  • 23,742
  • 39
  • 159
  • 250
  • The word I'm missing in your question is: [transaction](http://www.datanucleus.org/products/datanucleus/jpa/transactions.html). – zapl Oct 31 '14 at 14:47
  • @zapl yes I'm using transactions in my application services, but how can I use transaction to solve my problem? – davioooh Oct 31 '14 at 14:49
  • 1
    It depends a lot if your users will do small change very often or seldom large change. If _small often_ then you can check if the data was modified and reject the change (it is lost). With _large seldom_ you've better to lock everything as soon as a user starts modifying the grid. You can allow other users a read-only access until the modification is done. – ForguesR Oct 31 '14 at 14:52
  • @ForguesR ok, so you are suggesting "Optimistic lock" for small changes and "Pessimistic lock" for large changes. Am I right? – davioooh Oct 31 '14 at 14:54
  • 1
    What do you want to lock? The individual values (Optimistic Locking won't work with your current truncate/create approach) or some higher level entity i.e. the set of rows as a whole. http://stackoverflow.com/questions/19454003/implementing-optimistic-lock-using-hibernate-and-spring/19456821#19456821 – Alan Hay Oct 31 '14 at 14:55
  • op: Yes, for the small change solution have a look at Alan Hay link. – ForguesR Oct 31 '14 at 14:58
  • @AlanHay I need to lock the entire grid, so I think the best approach for me is to apply the pessimistic way... – davioooh Oct 31 '14 at 15:09
  • You could apply the optimistic lock on a higher level entity without any issues however I'd be thinking about User Experience: if I make a load of edits but then can't commit them due to concurrent edit then I think I'd rather be prevented from starting the edit in the first place. – Alan Hay Oct 31 '14 at 15:14
  • op: optimistic locking can work if you have a super entity for the whole sheet. – ForguesR Oct 31 '14 at 15:16
  • @AlanHay I completely agree with you. I'm looking for the best way to avoid the situation you explained. – davioooh Oct 31 '14 at 15:18
  • @ForguesR Optimistic locking uses entity version, Am I wrong? so how can I handle version in an high level entity that is not persisted? – davioooh Oct 31 '14 at 15:20
  • 1
    http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking – Alan Hay Oct 31 '14 at 15:20
  • op: You'll have to persist it somewhere. Have a look at Alan Hay link. – ForguesR Oct 31 '14 at 15:55

1 Answers1

1

The solution would probably depend on the behavior requirements.

How about the following scenario: users A and B started to change some values, then user A pressed Save button and saved data, after that user B did the same. User B got an error message saying something like "the data has been updated, please reload the page". He reloads the page and lose all changes he did :( Only after that he is able to save his changes, but he has to do it once again.

Other possible scenario: users A and B accessing the page, but only the user who was the first will be able to save his work, other users will see message saying something like "someone else is editing the page, try again later".

For the first scenario you can implement the following: each line of the table (in database) has a last-update-timestamp which is updated to current time each time this row is changed. Now, let's imagine user A get row with timestamp 1 when opened the page, user B was a little bit slower and got the same row with timestamp 2. But, he did his changes faster and pressed Save button first. Now, the row is saved in DB with timestamp let's say 5. User A is trying to save his changes, but the timestamp of his data is 1, which is different from 5 currently in DB. That means someone changed that data already and he should see error message I mentioned above.

Second scenario is a little bit harder to implement. I think the best way to do this is to open transaction to DB which

  • reads the row(s) we want;
  • put some flag like "locked" to true for all of them;
  • if some row is locked already, fails (or return available rows, depending on what you need). But, probably should fail;
  • returns rows to jsp page;

Now, if other user requested the same rows, transaction will fail and he will not be able to start changing data.

User A should put these locked flags back to false when he saves the data. Important thing: these locks should have timeout to prevent situation when user opened the page and closed it without saving (or browser crash, or something else). You may also want to implement some kind of lock reackquire for the same user - when user opened the page for the first time, then closed it without saving data and opened once again - he should be able to edit the data. This can be done by identifying user somehow - login, cookie, and so on.

Alexey Malev
  • 6,408
  • 4
  • 34
  • 52