Hello,


Taken one table, 3 columns (number), 6 records :
A B C
101 1 100
102 1 100
103 1 100
104 1 100
105 1 100
999 9 999

1) In session1 (using JDBC), a statement is created that will fetch the 5 first records with FOR UDPATE
and the usage of MIN() function on C column in the where clause, before deleting these records.
pseudo code :
Statement : SELECT B FROM POC WHERE A>100 and C=(select min(C) from POC where A>100) FOR UPDATE;
WHILE ResultSet.next()
WAIT 5 seconds (simulated some work)
DELETE (current) RECORD
END LOOP


2) In session2 *DURING* the above loop, an SQL query is updating the C column on *ANOTHER* (non locked) record
(and that's the important part) with a value *LOWER* than the actual MIN(C) existing in the table
UPDATE POC set C = MOD(A,100) WHERE A>100 and C=(select max(C) from POC where A>100);
COMMIT;


Result ==> "Value conflicts occurs" exception occurs once the seconds session has commited,POC_Value_Conflict(thru_OJDBC).zip
during the next ResultSet.deleteRow() on the first session.

POC JDBC there : http://pastebin.com/E9WsnsKD (java file with mostly comment how the use it ! Please read !)
(zipped source + compiled jar for non-paranoid people available attached or a few days there : http://ovh.to/hNx461V )


According to Oracle DOC (see full comment in the pastebin address), a Forward-Only resultSet should not see anything that is done externally
(I understand it as in the underlying DB, not in the ResultSet itself)...
Furthermore the session2 is not modifying any record's ROWID of the table, nor accessing any locked records, so this should not harm the ResultSet ?????


Moreover, similar (?!) process is working when done in PL-SQL (with a cursor in a package for the java code).
POC PKG there : http://pastebin.com/WT6aZZha ( POC.sql to be launched in session1 + one query to launch in session2 ).


I doubt this is a bug, but I don't know where I've made a mistake, nor if using min() in a where clause in non aggregate query is an error.
Neither I understand why it is working in one case and not the other.


Anyone with a clue is welcome to answer !


Thanks & Regards !