I ran the following jdo query, it is intended to get a list of entities that has empty "flow id". However, when I check the returned list, it did contain an entity with non-empty flow id.
PersistenceManager pm = pmf.getPersistenceManagerProxy();
String flowId = "";
Map<String, Object> params = new HashMap<>();
params.put("flowId", flowId);
List<MyEntity> entities = pm.newQuery(MyEntity.class, " this.flowId == :flowId ").setNamedParameters(params).executeList();
It doesn't always happen, but when it happens, I found there is always an update to that entity from another process to clear the "flow id" at a similar time. However, the result I got from the above query have that entity but shows a non-empty flow id. I also checked the JDO object state of the unexpectedly returned entity, it is persistent-clean. The query is run within an active transaction.
Here are the SQL compiled by JDOQLQuery.
SELECT 'com.example.MyEntity' AS "NUCLEUS_TYPE","A0"."CREATE_TIME","A0"."DATA_MAX_TIMESTAMP","A0"."DATA_MIN_TIMESTAMP","A0"."ID","A0"."OBSERVATION_ID","A0"."PARTITION_VALUE","A0"."PARTITION_CYCLE","A0"."PARTITION_TIMESTAMP","A0"."FLOW_ID","A0"."PROCESSING_STAGE","A0"."PROCESSING_STATUS","A0"."RECORD_COUNT","A0"."UPDATE_TIME" FROM "MY_ENTITIES" "A0" WHERE "A0"."FLOW_ID" = ?
Although I don't think it is relevant, the isolation level is read-committed, the entity is detachable, and that query above is running within a transaction. Please help, thanks!
Update
After I change the isolation level to repeatable-read, it never happens again. So highly likely it is related with isolation level. I'm not sure whether there is a bug or not. My data nucleus version is 4.1.6. Any thoughts will help.
I turn off both level1 and level2 cache. It seems caching in datanucleus doesn't work in some rare cases with multiple processes updating the same row with read-committed isolation level.
When the data store is applying the filtering condition, it can see the latest update from another process (set the flow_id to empty in my example). So it returns that row. However when datanucleus lookup its field using the row id, it checks the cache first but there is a potential stale value in cache for that entity, which seems to be the root cause of this issue.