Prevent two users from submitting the same form data from separate sessions

Question

My application is a form-based application, where users can raise a request, an ID will be assigned to that request, the user will fill out some details, and then submit it through different stages. The issue that multiple users can be a part of one request, so there might be a case where user A and user B is looking at the form at the same time. User A may submit first, and then user B submits, which would cause the request to only show the info submitted by user B. To solve this, I've come up with two solutions:

-Before we submit the data to the DB, we make a get call to see if the data is already submitted (we uses statuses in our app. So we would check for the status of "Stage # Pending").

-We implement a Redis Cache for some other services. I could add something like

public submitRequest() {
  RMap<String, int> requestLockMap = redissonClient.getMap("requestLock");
  if(requestLockMap.get('request_ID') == 0) {
     requestLockMap.put('request_ID', 1);
     ...continue submitting request...
  }
}

This is essentially adding in a racing condition with the Redis Cache. However, I don't know if either is a good practice for this sort of thing. Are there any better alternatives? I am using Spring as our Java framework, so if there are any solutions to that, I'm open to hearing them.

If you use hibernate, it already has in built version check if it is a update. Why dont you use it. — Ashraff Ali Wahab, Nov 26 '18 at 21:39
@AshraffAliWahab We define our own SQL using sql server, isn't hibernate for Java? — user3334871, Nov 26 '18 at 21:41
Hibernate is for Java connecting to any RDBMS.so SQL server is not an issue. — Ashraff Ali Wahab, Nov 26 '18 at 21:42
@AshraffAliWahab Right, but don't you define your query procedures using the Hibernate framework? All of our stored procedures are defined in the sql server we are running. We can port the procedures to Java and code them with Hibernate in mind, but that will take a monumental effort. Or can I incorporate Hibernate into our Java project and just use it to call the stored proc name? — user3334871, Nov 26 '18 at 21:45
Ok got it. That would be too much change. Look at this link about application lock in SQL server stored procedures based on transaction/session. https://www.sqlteam.com/articles/application-locks-or-mutexes-in-sql-server-2005 — Ashraff Ali Wahab, Nov 26 '18 at 21:50

score 1 · Answer 1 · answered Nov 26 '18 at 21:58

What would be the downside of assigning a randomly generated ID for each submission?

For example using UUID

UUID.randomUUID().toString()

you reduce the chance of identical request IDs to statistically 0. DB colissions can be treated as exceptions, as such scenarios are unlikely to happen.

Using this approach you can also avoid scenarios when unauthorized users can see / edit adjacent information by using a previous request ID.

score 1 · Answer 2 · answered Nov 26 '18 at 22:01

Don't follow your second solution. It will most likely lead to a race condition as you say.

Your first suggestion is correct. The key is to make the update inside a transaction and while being there check if the status is the expected so you may proceed with the update.

The status is similar to having a version id. When an entry is updated the version id is incremented. So a following/parallel edit will have an invalid version number or status and won't proceed. In your case if the status is not pending you will not proceed with the update.

Being inside a transaction you may rollback and return an error message to the user, i.e. the request has been edited by user X.

However, if there are many statuses you should better use a version id instead of the status.

An alternative way is to add the desired status in the where clause of the update statement as shown here to avoid updating a modified row.

Hibernate has a build in mechanism to achieve optimistic locking. See this for details.

score 1 · Answer 3 · answered Nov 26 '18 at 22:11

I'd suggest to have a timestamp field in the table, that the users are updating. When you show the form to user, read the timestamp and keep it in session. When updating, do something like update table A set some_column = 'value', timestamp = now() where id = 1 and timestamp = 'whatever timestamp you read earlier'. Then look at the return of the update statement, if it's 1, you updated the row, if it's 0, the other user has already updated - show some error/notification to the user. Ofcourse, you'll need to update the timestamp field during each update.

score 0 · Answer 4 · answered Nov 26 '18 at 22:01

One solution is to use an auto-increment integer field. When you fetch the record that will have one value and you include that in the form information. When the form is submitted, the record is read to see if it sill has the same value. If so, commit. If not, reject and send an error to the user that they are too late.

Since the field is automatically incremented on every commit, you don't have to do anything other than make sure that the submitted form includes the same value for that field.

This approach is not really user-friendly. For small systems with few users, this scenario rarely happens, but think in term of hundreds of concurrent users / systems — nucandrei, Nov 26 '18 at 22:03

Prevent two users from submitting the same form data from separate sessions

4 Answers4