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 blocking behaviour is an
>> issue only in read committed mode, right?
>
> I found your comment very interesting because it made me realize
> that I hadn't been thinking about how concurrency control and
> isolation levels play into this analysis.
Oh, no! Just when I started thinking they were irrelevant... :)
> So, a question, and an observation:
>
> 1) When you did the original analysis, are you sure that all
> 4 configurations that you tested (Derby client, DB2 client, MySQL,
> and Postgres) were using exactly the same isolation level and
> transaction settings? If you accidentally had one of your tests
> running with different settings, the measurements could have been off.
I ran them with default isolation levels: read committed in Derby and
PostgreSQL, and repeatable read in MySQL/InnoDB. I think isolation
levels would play a role if ResultSet.close() were to be piggybacked
on a subsequent JDBC call, but I don't see how they could affect the
number of round trips if my pre-fetch and implicitly-close hypothesis
is correct. At least when auto-commit is off.
Anyway, MySQL has the highest isolation level, but still has fewer
round trips than Derby.
> 2) Isn't it true that Postgres uses a versioning-based implementation
> of isolation, rather than a locking-based implementation?
PostgreSQL doesn't have any read locks. Writers create a new copy of
the data, and readers just read the old copy. Writers have to do some
kind of locking to ensure that only one transaction is updating a row.
> If so, that
> may have major implications for a benchmark like this, because Postgres
> is basically trading off increased complexity at the server side for
> less round-trips to communicate things like lock requests and releases.
Yes, it may, but I don't think it does. And regardless of what
actually happens in MySQL and PostgreSQL, I think a single-record
select using the Derby client driver should have no more than two
round trips: one to fetch the data and one to end the
transaction. Hopefully, I'll have time to get around and start looking
closer at it next week.
--
Knut Anders
