When we access the same table through entity bean and through direct SQL you have problems.
Case 1: data modified through entity bean and subsequently read/modify the same data through SQL - This will fail in most app servers as most of the app servers delay the entity bean SQL untill the end of transaction.
You can work around this in WebLogic via the following tag in weblogic-ejb-jar.xml
Then you will loose most of the caching advantage.
Another work around (again for weblogic) is to call a non PK finder before doing a direct SQL. Non PK finder by default (make sure you have not disabled this) flushes all modifications to database before finder is called.
Case 2: data modified through direct SQL and subsequently read through entity bean. If the same entity bean is accessed before the direct SQL (see the sequence below)
directSQL - change Name
Entity bean access after the direct SQL will not reflect the modifications done by direct SQL.
This is because entity bean loads the data only once at the beginning of the transaction.
There is no easy solution for this.
I heard that if you load the entity bean again using a non PK finder it loads again - not sure; and it is unlikely. Also if you have already modified the entity bean, then you are lost.
Bottom line is never access the same table though entity bean and direct SQL.