Lps 42599

Brian, this is a very critical bug, which pretty much affects all XXXFinderImpl.java

The root problem is, hibernate's SQLQuery is not like its Query, Query is based on QueryPlan which can easily figure out what tables this select is against, then before the select, flush out any previous change that may affect the current select. SQLQuery is just a thin wrapper on top of JDBC, it does not understand the content of the given sql query, which means we need to tell it what tables this query involves in order to help it to do a proper auto flush. This behavior is clearly documented on org.hibernate.SQLQuery javadoc since 3.6 version.

So all of our XXXFinderImpl is technically affected by this, in order to fix it, we have to manually add the synchronized info to the SQLQuery, see my demo fix to AssetTagFinderImpl.doCountByG_C_N()

@matthewkong found this bug through this method.

Although this is technically critical, but luckily, in most cases, it won't really be noticed. Because we rarely do a custom sql query right after a session based update, if we do any Query select before SQLQuery select, the session cache will be flushed anyway, then it won't cause any problem.

But anyway, we need to globally fix our XXFinderImpl SQLQuery usage, there are too many of them, they are very easy to fix, just too much manual work, please have someone else to apply this globally.