Knut Anders Hatlen wrote: >Bryan Pendleton <[EMAIL PROTECTED]> writes: > > > >>Knut Anders Hatlen wrote: >> >> >>>I haven't been thinking about the details, but at least it seems like >>>this is what PostgreSQL and MySQL do. >>> >>> > >I think I may have jumped to the wrong conclusions here. I don't think >PostgreSQL and MySQL are piggybacking ResultSet.close() on commit (and >I'm starting to agree with Dag that it's not desirable). Instead, I >think the entire result set is pre-fetched in executeQuery() (given >that the size of the result set is smaller than the default fetch >size), and if the result set is forward only, it can be closed on the >server side. > >I have looked more at the behaviour of Derby's client driver. A simple >select query seems to be executed like this: > > (Let's for the sake of argument assume that the result set has N > rows, where N is smaller than the default fetch size, and that we > are using forward-only result sets.) > > 1. PreparedStatement.executeQuery() is called. The client has one > round trip to the server opening the result set. No rows are > fetched. > > 2. ResultSet.next() is called. The client has one round trip to the > server fetching all the rows. The result set on the server side > is exhausted, but still open. > > 3. The next N-1 calls to ResultSet.next() use the pre-fetched rows > and do not generate network activity. > > 4. A last call to next() (which returns false) will have one round > trip, and the result set on the server is closed. The result set > on the client is still open. > > 5. ResultSet.close() is called. Since the result set on the server > is already closed, no network activity is generated. The result > set on the client side is now closed. > > 6. Connection.commit() is called, and a round trip to the server is > triggered. The transaction is committed on the server. > >This is how I understand the code and interpret what I see on the >network. Does anyone know if this is what actually happens? > >Assuming I have got it right this time, this is what I propose that we >try to do: > > a) Pre-fetching of rows should be done in executeQuery() instead of > the first next() call. > > b) Forward-only result sets should be implicitly closed on the > server side when all rows have been fetched by the client, so > that the client can assume that the result set is closed on the > server when it reads end-of-data. > >The DRDA protocol supports both a) and b), and there seems to be code >for both of them in the network server. So in theory we can achieve >this just by changing the client driver. > > > This sounds like a correct analysis and a good solution. A couple of areas of vague concern:
You might want to consider if there is any impact on the fix for DERBY-213. I think it should be fine, but popped into my mind as a red flag. Large objects tend to be very sensitive to these types of changes, so you might want to give them some thought for various scenarios especially when autocommit is on. Sorry I can't offer anything more concrete. Thanks for looking at this. Kathey
