java.sql.SQLException: ORA-01002: fetch out of seq

I have one demo application to check select for update query

public class Test implements Runnable{

    public Test() {
    }

    public static void main(String[] args) {
        Thread t1 = new Thread(new Test());
        Thread t2 = new Thread(new Test());

        t1.start();
        t2.start();
    }

    public void run() {
        // TODO Auto-generated method stub
        try{
            String updateWith = "";
            String sel = "SELECT SIDNUMBERTO FROM tblmserialbatchdetail WHERE sidnumberto = ("+
            "SELECT max(sidnumberto) FROM tblmserialbatchdetail WHERE generationmode='A' and nvl(serialprefix,' ') = nvl('',' ') " +
            "and sidlengthwithoutprefix =12) FOR UPDATE";
            //System.out.println("SELECT QUERY ===: "+sel);
            String updatequery = "update tblmserialbatchdetail set sidnumberto = ? where serialbatchid = ?";
            System.out.println();
            Connection connection = Conn.getDBConnection();
            PreparedStatement pStatement = connection.prepareStatement(sel);

            ResultSet rSet = pStatement.executeQuery();

            while(rSet.next()){

                updateWith = rSet.getString(1);
                long value = Long.parseLong(updateWith)+1;
                updateWith = String.valueOf(value);
                System.out.println("resulet To be Updated ===> "+value);
            }
            connection.commit();
            connection.close();



        }catch (Exception e) {
            e.printStackTrace();
        }
    }

}

This works fine if i remove For update from select query otherwise give me error

java.sql.SQLException: ORA-01002: fetch out of sequence

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
    at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2119)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:421)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:365)
    at com.response.Test.run(Test.java:47)
    at java.lang.Thread.run(Thread.java:595)

回答1:

SELCT ... FOR UPDATE only makes sense in the context of a managed transaction, since it requires locks to be taken out on the selected rows.

By default, JDBC does not use a managed transaction, it uses an implicitly created one that commits as soon as the query is executed. This will break the semantics of SELECT ... FOR UPDATE, and the JDBC driver complains.

In order to use a managed transaction, add

connection.setAutoCommit(false);

before you execute the query. Afterwards, execute connection.commit().

回答2:

Try:

connection.setAutoCommit(false);

As a general note, 'select ... for update' is not the best locking strategy.

回答3:

instead of using executeQuery, you can use executeUpdate function. Do not use "select for update" instead directly try to update the rows in one transaction, This will give you the updated rows count. This way for example when two transaction tries to update a row , first one wins other returns zero and you know that first succeeds to update since it will return updated rowcount.