2

If I have a simple Java program that generates a prepared statement to execute SQL like

select * from person

and this prepared statement returns a ResultSet, what is the behavior when iterating through the result set if another process is writing rows to the person table at the same time?

For example, will the newly added rows be visible inside the following loop

while (resultSet.next()) {
    // Some processing 
    // Which might take a while
}

I have read elsewhere that it is possible to use setFetchSize to control the number of rows that are returned in the ResultSet.

If I use this method does this change the answer? (assuming rows are written faster than they are read and processed)

mshirlaw
  • 168
  • 1
  • 10

2 Answers2

1

From the Oracle documentation:

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row. The method ResultSet.next moves the cursor to the next row. This method returns false if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next method with a while loop to iterate through all the data in the ResultSet.

See: https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

With regard to your second question:

What and when should I specify setFetchSize()?

So no, setting the size for how much heap is to be allocated does not change semantics.

Koenigsberg
  • 1,726
  • 1
  • 10
  • 22
0

In principle this would depend on the driver implementation. However in reality, drivers will either load the entire resultset into memory, or use a db cursor to fetch more results into the set.

In both cases, inserts happening in parallel would be invisible. A database cursor normally does not see inserts that are committed after it was created.

Joeri Hendrickx
  • 16,947
  • 4
  • 41
  • 53