db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Message round-trips between Derby client and server
Date Wed, 11 Jan 2006 10:48:53 GMT
"Dag H. Wanvik" <Dag.Wanvik@Sun.COM> writes:

> Hi,
>>>>>> "Knut" == Knut Anders Hatlen <Knut.Hatlen@Sun.COM> wrote:
> Knut> 
> Knut>                                 | Derby | DB2 univ | Postgres | MySQL
> Knut> ---------------------------------------------------------------------
> Knut> DriverManager.getConnection     |   X   |    X     |    X     |   X
> Knut> Connection.setAutoCommit(false) |       |          |          |   X
> Knut> Connection.prepareStatement     |   X   |          |          |   X
> Knut> ---------------------------------------------------------------------
> Knut> PreparedStatement.executeQuery  |   X   |    X     |    X     |   X
> Knut> ResultSet.next                  |   X   |    X     |          |
> Knut> ResultSet.close                 |   X   |    X     |          |
> Knut> Connection.commit               |   X   |    X     |    X     |   X
> Knut> 
> Knut> The first three methods are not candidates for optimization, in my
> Knut> opinion, since a well-written application won't call them
> Knut> frequently. But I think we should do something with the last four
> Knut> methods.
> Knut> 
> Knut> My initial thought is that we should try to:
> Knut> 
> Knut>   1) Pre-fetch rows in PreparedStatement.executeQuery(), saving one
> Knut>      round trip per select statement.
> Knut> 
> Knut>   2) Delay ResultSet.close() and send it piggybacked on the next JDBC
> Knut>      call, saving one round trip per select statement when autocommit
> Knut>      is off.
> If the next JDBC communication won't take place for a while, this
> second optimization can significantly delay the release of result set
> locks, so I would be hesitant to recommend this optimization (as a
> default behavior, anyway), since other transactions might block,
> without the user realizing the cause of it.

I haven't been thinking about the details, but at least it seems like
this is what PostgreSQL and MySQL do. This blocking behaviour is an
issue only in read committed mode, right? In read uncommitted there
are no shared locks, and in repeatable read or serializable the read
locks won't be released until commit or rollback anyway. In read
committed mode I think the shared lock on a row is released when
ResultSet.next() steps to the next row. Therefore, the only case I can
think of where delaying ResultSet.close() would cause more blocking is

  1) auto-commit is off, and
  2) the isolation level is read uncommitted, and
  3) the cursor in the ResultSet on the server side is on a valid
     position (i.e. not exhausted), and
  4) there is a long interval between ResultSet.close() and the next
     JDBC call, and
  5) another transaction tries to obtain an exclusive lock on the row
     that is still locked.

Is this a common scenario? I think the ResultSet in most cases will be
exhausted on the server side because of the pre-fetching done by the
client driver. And in any case I believe that most applications either
commit or execute a new statement right after ResultSet.close().

Knut Anders

View raw message