0

When an existing user makes a request the method first deletes the oldest record before saving the new request. The code below works fine IF the requests don't come in too rapidly (using an Oracle DB).

   public Request saveRequest(Request req)
   {
      String user = req.getUser();
      
      // Do a NamedQuery on the entity to pull all requests for user
      List<Request> requestList = getRequests(user);
      
      LOGGER.info("Request List size is " + requestList.size() + " for " + user);
      
      // Sort the list then delete the first/oldest request
      Comparator<Request> bySessionDate = Comparator.comparing(Request::getDate);
      Collections.sort(requestList, bySessionDate);
         
      LOGGER.info("Deleting request id " + requestList.get(0).getId());

      deleteById(requestList.get(0).getId());

      Request sreq = requestRepository.create(req);
      LOGGER.info("Saved request for user " + sreq.getUserId());
      return sreq;
   }

Output:

2021-01-20 00:39:12,167 INFO  [beez.service.RequestManager] (default task-4) Request List size is 250 for Bob
2021-01-20 00:39:12,168 INFO  [beez.service.RequestManager] (default task-4) Deleting request id 757A9B21E51D49199F2E182F68BC6BF7
2021-01-20 00:39:12,171 INFO  [beez.service.RequestManager] (default task-4) Deleted: 757A9B21E51D49199F2E182F68BC6BF7
2021-01-20 00:39:12,173 INFO  [beez.service.RequestManager] (default task-4) Saved request for user Bob
2021-01-20 00:39:15,375 INFO  [beez.service.RequestManager] (default task-3) Request List size is 250 for Bob
2021-01-20 00:39:15,375 INFO  [beez.service.RequestManager] (default task-3) Deleting request id 27239B85472C45EDA5495E98523295F3
2021-01-20 00:39:15,377 INFO  [beez.service.RequestManager] (default task-3) Deleted: 27239B85472C45EDA5495E98523295F3
2021-01-20 00:39:15,380 INFO  [beez.service.RequestManager] (default task-3) Saved request for user Bob

However, if the user were to submit requests in a very quick fashion, i.e. happy clicker, the same code generates a StaleStateException error.

2021-01-20 00:42:31,307 INFO  [beez.service.RequestManager] (default task-3) Request List size is 250 for Bob
2021-01-20 00:42:31,307 INFO  [beez.service.RequestManager] (default task-3) Deleting request id 55E43DF4D83E4BF5AD73DE47A49B0DA9
2021-01-20 00:42:31,310 INFO  [beez.service.RequestManager] (default task-3) Deleted: 55E43DF4D83E4BF5AD73DE47A49B0DA9
2021-01-20 00:42:31,313 INFO  [beez.service.RequestManager] (default task-3) Saved request for user Bob
2021-01-20 00:42:31,332 INFO  [beez.service.RequestManager] (default task-7) Request List size is 250 for Bob
2021-01-20 00:42:31,332 INFO  [beez.service.RequestManager] (default task-7) Deleting request id 55E43DF4D83E4BF5AD73DE47A49B0DA9
2021-01-20 00:42:31,492 ERROR [org.jboss.as.ejb3.invocation] (default task-7) WFLYEJB0034: EJB Invocation failed on component RequestManager for method public beez.entity.RequestManager service.RequestManager.saveRequest(beez.entity.RequestManager): javax.ejb.EJBTransactionRolledbackException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
.
.
.
Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

Seems like the code hasn't had time to complete the change before the next one came in causing the code to try to delete the same record twice. Other than changing the front end or something else before this method, is there a way around this?

I've tried the @Transactional and @Lock options with no success. Spent a lot of time in this thread but the solutions either didn't work or didn't apply: Hibernate - Batch update returned unexpected row count from update: 0 actual row count: 0 expected: 1

stackbacker
  • 329
  • 2
  • 11

1 Answers1

1

If users clicking rapidly is not expected, strongly recommend you to think of Debouncing API requests in front to skip back to back requests/events.

As you said rightly issues is happening because of inconsistency between what entities loaded to a individual session and current state of database.

This can be solved in multiple ways like using pessimistic locking with (select for update) comes with performance bottle necks, synchronizing the method .... etc

Simplest way to handle this is deleting using a JPA query and ordering at database level, so this always works on the current state of record table.

delete from request where id= (select req.id from request req left join user usr on usr.id = req.userId where usr.userId=? order by req.date LIMIT 1)

Please correct the above query according t your Entity design.

Srinivas karre
  • 66
  • 6
  • I tried your suggestion but the same problem exists when the user makes a single submission that contains multiple requests. Each request is received by the code as a single request one after another anywhere between 20-30 ms apart. So the code hasn't completed the delete before the next request comes in which also triggers a delete for that user. It seems to me that if I try to implement locking or synchronization I'd be doing it for all users which would create a huge bottleneck, i.e. User B would have to wait for User A's process to finish, etc. – stackbacker Jan 22 '21 at 20:45
  • I feel like this shouldn't be a unique problem. You have a system that serves multiple users making frequent/multiple requests that are saved/deleted in a database. Right now, the only solution I'm seeing is to modify the front end by implementing a debouncer for the happy clickers and sending all requests in a batch rather than blasting them at the service individually. – stackbacker Jan 22 '21 at 20:45
  • @stackbacker delete query should not throw stale state as we are running this on database directly rather than deleting a particular entity. One more approach could be start an inner transaction for this operation and commit right away but this can be reverted depending upon your outer transaction boundary. – Srinivas karre Jan 23 '21 at 12:28
  • You are absolutely correct. The same error does not exist...I was distracted by a new problem that popped up related to the same issue. I'm marking this as the solution. Thanks! – stackbacker Jan 23 '21 at 18:40
  • Thanks @stackbacker, test the behavior for first time user who doesn't has any request created yet, this covers the edge cases like first time user and if there is one request in database and you are trying to delete from in two different transactions – Srinivas karre Jan 24 '21 at 05:05