IncorrectUpdateSemanticsDataAccessException: Id not found when saving an unchanged Entity

[joseph415]

Environment:

• Spring Boot: 3.3.4
• Spring Data JDBC: 3.3.4
• Database: MySQL 8.0.28 (InnoDB)

Description

In our service, we accidentally encountered a case where save() was called on an entity that had no modified fields.

Because the entity data was identical to the data in the database, the MySQL (InnoDB) engine performed an optimization and reported 0 for the number of affected rows from the UPDATE statement.

This 0 affected rows count subsequently caused Spring Data JDBC to throw an exception:
Caused by: org.springframework.dao.IncorrectUpdateSemanticsDataAccessException: Failed to update entity [...]; Id [...] not found in database

The UPDATE statement (from DefaultDataAccessStrategy.java) returns 0 affected rows:
https://github.com/spring-projects/spring-data-relational/blob/main/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/convert/DefaultDataAccessStrategy.java#L155

This then triggers the exception here (in JdbcAggregateChangeExecutionContext.java):
https://github.com/spring-projects/spring-data-relational/blob/main/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/JdbcAggregateChangeExecutionContext.java#L350

This appears to be a bug, because the entity does exist in the database; it just wasn't modified by the UPDATE statement. Is it possible to correct this behavior so that an UPDATE with 0 affected rows on an existing entity does not throw an exception?

[schauder]

Not throwing an exception there at list for MySql, would resolve the issue in your scenario.

Thinking about optimistic locking I see the following cases:

• in the normal case the version has changed, so a normal update count of 1 would get reported.
• in the normal concurrent case update is 0 and we should throw an exception.
• in the abnormal concurrent where we actually try to write the exact same values that are already in there from a concurrent write, we get an update count of 0 anyway, because the version does not match the one we are using for the optimistic locking check.

If I didn't miss anything, we are fine with not throwing an exception. The question remains: do we not want to throw an exception for MySql only (and other databases behaving like this) or should we never throw an exception?

[joseph415]

@schauder Thanks for the quick response.

I am currently using Vitess and experiencing an IncorrectUpdateSemanticsDataAccessException. According to this issue (vitessio/vitess#5041), it seems the exception is being thrown because Vitess defaults to useAffectedRows=true.

I believe this behavior (an UPDATE returning 0 affected rows when no values are changed) is not exclusive to MySQL, and that this same phenomenon can likely occur in various other databases as well.

given this, it seems better to stop throwing an exception in all such cases (not just for MySQL). What do you think?

Alternatively, improving the exception message would also be a good solution. I experienced a lot of confusion because of the "not found in database" message, which initially led me to believe I had a transaction isolation problem. A more accurate message would be extremely helpful.