返回PostgreSQL事务中SELECT语句的结果

Question

我想这样做:

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

但是这个查询返回一个空的ResultSet在JDBC中执行从准备语句的查询后，是否可以在事务中包装SELECT语句?或者我应该完全改变方法吗?

基本上，我需要它的行为完全像select一样(性能方面也是如此)，但如果表上有排他锁就会失败。

编辑:

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

Java代码:

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));
}

Answer 1

为了澄清我在注释中所说的内容，我将使用JDBC来控制事务。它可能看起来像，

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);
}

那么你可以使用

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