(ObjectOptimisticLockingFailureException) StaleStateException in a single transaction

Question

The code below is deliberately simplified and abridged to avoid unnecessary distraction.

I've mysql database with table:

CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(45) DEFAULT NULL,
  `last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

I have Spring Transactional method which is called from the controller.

@GetMapping("/reproduce_error")
    @Transactional
    public ResponseEntity reproduceErrorOnUpdate(Long id) {
        try {
            Payment payment = paymentDao.getPayment(id); //1st query
            payment.setStatus(payment.getStatus().equals("A") ? "B" : "A");
            paymentDao.findAllByStatus("NOT EXISTING STATUS"); // 2nd query
            payment.setStatus("C");
        } catch (Exception e) {
            return new ResponseEntity<>(e, HttpStatus.INTERNAL_SERVER_ERROR);
        }
        return new ResponseEntity(HttpStatus.OK);

    }

When I call this method I always have an exception:

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

BUT AT THE SAME TIME (and it is the most curious thing):

if we omit the 2nd query (which anyway returns null!), the method works correctly!

Both methods are executed at the SINGLE transaction! (here is how I call them both, so you can see that there are no other transactions somewhere)

There are no different threads or HTTP calls working with the same DB row (as per I reproduce it on my local machine). The only difference is that in the 1st case (error) we make the second search at the DB and have an error (even if this second search returns nothing) and at the 2nd case (success) we don't do so.

I understand that (in error case) Hibernate flushes changes before the 2nd call to DB is executed to support consistency. But why then we have StaleStateException as we DO process everything it ONE transaction and every changes made in it should be visible for itself.

Could someone help me with this weird hibernate behavior and explain why this happens.

Answer 1

I've prepared the minimal reproduceable app and placed it on bitbucket: https://bitbucket.org/gegunov/hibernate_issue

I've also raised a ticket at Hibernate project's Jira: https://hibernate.atlassian.net/browse/HHH-13867

The Hibernate team has helped me with it. It turned out that this issue was a result of both, Hibernate and MySql bugs connected with precision losses during the flush.

One of the workarounds is to change DB column format from TIMESTAMP to (e.g) TIMESTAMP(6) or DATETIME(6).