FlushMode.AUTO doesn't flush on SQLQuery

Description

As states in Hibernate documentation:

AUTO - The Session is sometimes flushed before query execution in order to ensure that queries never return stale state.

In the attached unit test I tested if this assumption stands still when an Entity is persisted and we are issuing an SQL query on the same database table:

Product product = new Product("LCD");
session.persist(product);
assertEquals(product.getId(), session.createSQLQuery("select id from product").uniqueResult());

The Product entity has an UUID2 identifier, so the id is generated right from the start. The entity resides in the 1st level cache waiting to be synchronized with the database (during the flush time).

If I ran the equivalent HQL query, a flush need is detected and the query will return consistent data.

This doesn't happen with the SQLQuery, and because AUTO only flushes "sometimes" the current event query space is empty:

private boolean flushIsReallyNeeded(AutoFlushEvent event, final EventSource source) {
return source.getActionQueue()
.areTablesToBeUpdated( event.getQuerySpaces() ) ||
source.getFlushMode()==FlushMode.ALWAYS;
}

The flushIsReallyNeeded returns false in this case.

Environment

None

Activity

Show:
Steve Ebersole
October 28, 2015, 4:35 PM

And for what its worth, that is actually exactly what I do for native query execution via JPA... I flush the entire Session to be compliant with that exact sentiment. However even there I exposed the ability to specify synchronize-tables for the native query via hints. The reason being that it is much more performant generally speaking.

As I said I am not against that per-se. But I never like to hurt the performance of users that do the right thing to accommodate users that do not do the right thing. To me this is a (new - enhancement) setting that is disabled by default (current behavior)

Vlad Mihalcea
October 28, 2015, 4:46 PM

In case there are pending changes, those would still be executed by the time the transaction is committed, which means there are two causes for any performance penalty:

  • the dirty checking mechanism (the default one is fine as long as the Session is not bloated with tons of entities)

  • the locks that might be acquired too early (even in MVCC, locks are still acquired to prevent write-write conflicts).

We could leave it like this, since it's been like that for a decade and just provide a comprehensive documentation to educate the user about the inconsistency risk.

Steve Ebersole
October 28, 2015, 5:29 PM

The more common case is to native-query against yet unaffected tables.

So what I can see here is to define a setting to allow this to work like I do now in HEM. For the sake of discussion, let's call this setting hibernate.auto_flush_before_native_query. IN pseudo-code we could say:

Sébastien Tardif
November 2, 2017, 10:58 PM
Edited

I think the doc could be better:
The Session is sometimes flushed before query execution in order to ensure that queries never return stale state. != The Session is sometimes flushed before query execution in order to ensure that sometime queries don't return stale state.

I think auto_flush_before_native_query==true should be the default. Default should be about having ACID properties without stale issues. Performance tuning should come after the application run correctly.

What added to the confusion for me is that flush doesn't occur when doing a native select, but when doing a native update the second level query cache IS flushed entirely! That's clearly show an inconsistency, likely a mistake/oversight... too far thinking we have a bug here?

Extract from BulkOperationCleanupAction:
private boolean affectedEntity(Set affectedTableSpaces, Serializable[] checkTableSpaces) {
if ( affectedTableSpaces == null || affectedTableSpaces.isEmpty() ) {
return true;
}

Vlad Mihalcea
November 3, 2017, 6:09 AM

That's no longer an issue since 5.2 if you use the JPA bootstrap, meaning you are using:

  • Java EE

  • Spring or Spring Boot with JpaTransactionManager (the vast majority of deployments)

So, unless you are bootstrapping with the native API (e.g. Spring or Spring Boot with HibernateTransactionManager), then the Session will be flushed prior to any query.

Assignee

Unassigned

Reporter

Vlad Mihalcea

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure