HIbernate “StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1”

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

Answer

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.