Mysql view is not updated inside a transaction (Hibernate / Spring)
I am using Spring for DI and Hibernate to access data in mySQL database. I have code inside a transaction that inserts a record into a table and then executes a view that queries that table and does some generalized calculations. The problem I see is that the record I just inserted during the same transaction is not included in the calculated view values. I am running the same view in mySQL workbench and the inserted value is included in the view. Does anyone know what is causing this?
source to share
I ended up having to call entityManager.refresh (the Object) to update the entity for the view record I wanted to update. I think the problem is that Hibernate cannot recognize that the view needs to be updated as it doesn't know that it depends (at the database level) on the original object that was updated. My guess is that Hibernate is caching records from the view and doesn't know they need to be updated even after a flush ().
Hibernate sees the original table and view as completely unrelated, when in fact the view depends on the table and must be dirty whenever the table changes. I don't know how to get Hibernate to recognize this.
source to share
This is most likely due to the default MySQL isolation level, which is REPEATABLE READ
.
This means that your MySQL Workbench transaction will not see the changes until you finish that transaction. Executing a SELECT is considered a transaction.
You should see the changes in MySQL Workbench after you commit (or rollback).
You probably want to change the default isolation level for your installation to, READ COMMITTED
or change the isolation level of your MySQL Workbench session toREAD COMMITTED
Details on how you do this can be found in the manual.
source to share