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

3 answers

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.



Chances are, you haven't flushed your database changes yet. Hibernate doesn't know that there is a relationship between the table you inserted the rows into and the view you read later. Perform flash noun before requesting a view. (Or session or pattern).



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.



All Articles