Summing up the problem's anatomy: With our JPA entities, we use one column version of the type datetime for optimistic locking through @Version, meaning that prior to each UPDATE, we verify that the most recently known version in the EntityManager corresponds to the one in the database. Starting with MySQL 5.6.4, fractional seconds with up to six decimal places (microseconds) are supported for datetime. In that process, datetime(0) is the default value:
The MySQL JDBC driver (Connector/J) is responsible for "translating" the JDBC statements into native SQL. With all versions prior to 5.1.23, milliseconds are always truncated - a bug that has already been discussed under MySQL Bug #60598.
If a MySQL version greater than 5.6.4 is used together with a Connector/J version greater than 5.1.23, the following will happen within one transaction: The dataset is created in a version containing milliseconds.
INSERT INTO table (version,...) VALUES ('2015-11-14 17:05:47.618',...)
The entity's internal version number in the EntityManager, consequently, contains milliseconds, too. They do not land in the database, though. In case of an UPDATE to the data set, it tries to be identified based on its id and version.
UPDATE table set ... where id=10 and version='2015-11-14 17:05:47.618'
This update fails, since the column "version" is of the type datetime(0), hence doesn't support milliseconds and the condition version='2015-11-14 17:05:47.618' is not fulfilled.
This leads to the notorious OptimisticLockException:
Tip: Since with Hibernate (as a JPA implementation) it is hard/laborious to get to the SQL actually generated, I recommend the JDBC-Performance-Logger for that. Not only does it show the SQL statements, but is also a huge help in identifying slow queries.
The solution to the problem, therefore, is to either add milliseconds to the version column or use a driver prior to 5.1.23.
But since we don't feel comfortable using an outdated driver for any lenght of time, let alone one that might still be using the forbidden SET OPTION statement, the version columns were updated to datetime(3).
Conclusion: Even with seemingly minor updates of MySQL and the JDBC driver, one should always study the Release Notes carefully in order to identify potential problems in good time.
Interestingly enough, this problem had been brought to Hibernate's attention a long time ago, as substantiated by Ticket HHH-3822. This ticket, though, has never been dignified with a response by the Hibernate team. It was five years later, with the solution of HHH-8401, that it was closed without comment.