I would like to do something like this:

BEGIN;
LOCK TABLE MY_TABLE IN ACCESS SHARE MODE NOWAIT;
SELECT * from MY_TABLE;
COMMIT TRANSACTION;

but this query returns an empty ResultSet after executing the query from prepared statement in JDBC, is it possible to wrap a SELECT statement in a transaction? Or should I change approach completely?

Basically I would need it to behave exactly like the select would (performance-wise too), but fail if there is an exclusive lock on the table.

EDIT:

Some context: Fail SELECT Query if table is locked in PostgreSQL (parent question)

Java code:

PreparedStatement sm = SimpleStatementWrapper.wrap(conn).prepareSelectStatementLockNowait(
        SQL,
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY
);
ResultSet rs = sm.executeQuery();

private String wrapSql(String sql) {
    String trimmed = sql.trim();
    return "BEGIN; "  
            "LOCK TABLE PING_TASK IN ACCESS SHARE MODE NOWAIT; "
              (trimmed.endsWith(Constant.SEMI_COLON) ? trimmed : trimmed   Constant.SEMI_COLON)
              " COMMIT TRANSACTION;";
}


// connectionCache is the same connection that was passed in .wrap(conn)
@Override
public PreparedStatement prepareSelectStatementLockNowait(String sql) throws SQLException {
    return this.connectionCache.prepareStatement(wrapSql(sql));
}

CodePudding user response：

To clarify what I was saying in the comments, I would use JDBC to control the transaction. That might look something like,

private static final String LOCKSQL = "LOCK TABLE PING_TASK IN ACCESS SHARE MODE NOWAIT";

@Override
public PreparedStatement prepareSelectStatementLockNowait(String sql)
            throws SQLException {
    this.connectionCache.setAutoCommit(false);
    try (PreparedStatement ps1 = this.connectionCache.prepareStatement(LOCKSQL)) {
        ps1.execute();
    }
    return this.connectionCache.prepareStatement(sql);
}

Then you can use

ResultSet rs = sm.executeQuery();
while (rs.next()) {
    // ...
}
conn.commit(); // commit the transaction