在多线程环境中使用select for update nowait时，只读结果集的操作无效

Ionuț U 发表于 Dev

爱奴ț

对于Oracle数据库，以下程序仅对某些线程抛出SQL异常。为什么将resultSetConcurrency从降级CONCUR_UPDATABLE to CONCUR_READ_ONLY？在单线程环境中不会发生这种情况。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class Main extends Thread {

    public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:DB";
    public static final String DBUSER = "USER";
    public static final String DBPASS = "PASS";

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        for(int i=0; i<20; i++)
            new Main().start();
    }

    @Override
    public void run() {

        try
        {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
            con.setAutoCommit(false);

            try(PreparedStatement pstmt = con.prepareStatement("SELECT COLUMN1 FROM TABLE1 FOR UPDATE NOWAIT", 
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE))
            {               
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    rs.updateString(1, "12345");
                    rs.updateRow();
                }            
            }
            finally
            {
                con.commit();
                con.close();
            }
        }
        catch(SQLException e)
        {
            if(!e.toString().contains("NOWAIT"))
                e.printStackTrace();
        }
    }

}

亚历克斯·普尔

您可以查看针对结果集/语句/连接提出的警告，以了解为什么将其降级。在executeQuery()通话后添加以下内容：

SQLWarning warning = pstmt.getWarnings();
while (warning != null)
{
    System.out.println("Warning: " + warning.getSQLState()
        + ": " + warning.getErrorCode());
    System.out.println(warning.getMessage());
    warning = warning.getNextWarning();
}

在这种情况下，您有时会看到：