Hi all,
I need to add optimisitic locking to some of my entities, which is quite straightforward adding a new @Version annotated column to the entity class. But I was wondering if I should also add an index to that column in database?
Why would you need that? Hibernate ORM uses the version in e.g. updates to ensure that the database state of a row was not changed between the read and update, but the update is still using the primary key in the WHERE clause. The database will ultimately also use the primary key index to find the row and apply the version predicate as filter afterwards, which is totally fine.
I thought the version is also used in the statement WHERE id = x and version = y?! (Found that info here: How does the entity version property work when using JPA and Hibernate - Vlad Mihalcea)
It is, but why do you think that an index on version would improve the performance for a database to lookup a row? The primary key index is unique, so an equality predicate on the primary key will result in exactly one row (highly selective). When that row is found, checking the version is a simple filter on the row data. There is no need to put the version into an index or create a special index for the version. An equality lookup by version is not very selective anyway. This about it, how many rows are there that would have the same version. Potentially hundreds or thousands, which makes the lookup by version not very selective. The database is smart enough to figure this out and will never use such an index, but rather find a row by primary key index.
Thanks for your clarification. It makes totally sense!