Michael-A-McMahon
commented
1 month ago With a FOR UPDATE clause, we can get ORA-01002 if we do this:
- Begin a transaction
- Execute the query with
FOR UPDATE - Commit or rollback the transaction
- Fetch more rows from the query.
Here's JDBC code to illustrate:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class ForUpdate {
public static void main(String[] args) throws Exception {
try (
Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@test");
Statement statement = connection.createStatement();
AutoCloseable dropTable = () -> statement.execute("DROP TABLE test");
PreparedStatement query = connection.prepareStatement(
"SELECT id FROM test FOR UPDATE SKIP LOCKED");
PreparedStatement insert = connection.prepareStatement(
"INSERT INTO test VALUES (?)"
)) {
statement.execute("CREATE TABLE test (id NUMBER)");
for (int i = 0; i < 100; i++) {
insert.setInt(1, i);
insert.addBatch();
}
insert.executeBatch();
// Transaction begins before executing the SELECT ... FOR UPDATE query
connection.setAutoCommit(false);
// Fetch 10 rows at time
query.setFetchSize(10);
// The SELECT ... FOR UPDATE query is executed
try (ResultSet resultSet = query.executeQuery()) {
// Transaction ends before all rows have been fetched
connection.setAutoCommit(true);
// ORA-01002 when JDBC (or R2DBC) goes to fetch the next 10 rows:
while (resultSet.next())
System.out.println(resultSet.getInt(1));
}
}
}
}If you know the number of rows this query returns, and you know that all row data can fit in memory, then you could pass the row count to Statement.fetchSize(int). This avoids R2DBC having to send multiple fetch requests to the database (so it's faster) and it might solve the issue of the transaction ending before all rows have been fetched.
But if the number of rows is unknown, or it is too much to fit in memory, then you'll want to make sure a commit/rollback doesn't happen until the Result publisher has emitted it's last row. Maybe the Flux.usingWhen operator could be useful here; You can end the transaction in the asyncCleanUp Function: https://projectreactor.io/docs/core/release/api/reactor/core/publisher/Flux.html#usingWhen-org.reactivestreams.Publisher-java.util.function.Function-java.util.function.Function- Something like:
Flux.usingWhen(
Mono.from(connection.beginTransaction()).thenReturn(0), // <-- need to emit a value to trigger query publisher
ignored ->
Flux.from(connection.createStatement(FOR_UPDATE_QUERY))
.flatMap(result ->
result.map(row -> createPfrStatus(row)), // <-- Your method for mapping row data into PrfStatus
ignored -> connection.commitTransaction())Code above isn't tested, but I hope the idea comes across: We don't commit/rollback until all rows have been received.
I have @Scheduler process, which select rows from Oracle database with option FOR UPDATE SKIP LOCKED every 30 seconds When table is empty i periodically catch exception ORA-01002 fetch out of sequence
Repository:
Dao method:
Stacktrace:
What could be the problem?
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.23.0.0.0