[
https://issues.apache.org/jira/browse/DERBY-5425?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13116600#comment-13116600
]
Knut Anders Hatlen commented on DERBY-5425:
-------------------------------------------
Reproduced on trunk. Some observations:
When an updatable result set uses an index, and a value is changed so that the
row may be found again later in the index scan (typically because the key value
is increased), the row location is stored in a hashtable to make it easy to
skip that row when it's seen again. When the size of the hashtable exceeds 1/16
of derby.language.maxMemoryPerTable (default: 1048576/16=65536 rows), a
TemporaryRowHolder is created to hold the overflowing rows. The TRH spills to
disk when its size exceeds 100 rows. That is, it spills to disk after
65536+100=65636 row locations have been stored, which is very close to the
number of rows seen by the repro.
If UpdateResultSet.notifyForUpdateCursor() is modified to use the hashtable no
matter how large it is, and never use the TemporaryRowHolder, the repro returns
the expected number of rows (100000).
If UpdateResultSet.notifyForUpdateCursor() is modified to create a
TemporaryRowHolder instance that holds more than 100 rows before it spills to
disk, the number of rows returned by the repro increases.
Increasing derby.language.maxMemoryPerTable also makes the repro return more
rows.
So it looks like the problem is somehow related to this overflow handling.
I also noticed that removing the commit() call after each row update in the
repro, all rows were returned. At the same time I noticed that the temporary
conglomerate created by TemporaryRowHolder is not holdable over commits (see
second argument to openConglomerate() call in TemporaryRowHolderImpl.insert()),
which sounds suspicious. However, simply hard-coding
TemporaryRowHolderImpl.insert() to create conglomerates that are holdable over
commit, didn't change the number of rows returned.
> Updateable holdable ResultSet terminates early after 65638 updates
> ------------------------------------------------------------------
>
> Key: DERBY-5425
> URL: https://issues.apache.org/jira/browse/DERBY-5425
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.7.1.1
> Environment: ------------------ Java Information ------------------
> Java Version: 1.6.0_26
> Java Vendor: Sun Microsystems Inc.
> Java home: D:\Program Files (x86)\Java\jre6
> Java classpath: .;..\derby.jar
> OS name: Windows 7
> OS architecture: x86
> OS version: 6.1
> Java user name: Andrew
> Java user home: D:\Users\Andrew
> Java user dir: E:\workspace\DerbyBug\bin
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> java.runtime.version: 1.6.0_26-b03
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [E:\workspace\DerbyBug\derby.jar] 10.7.1.1 - (1040133)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> ------------------------------------------------------
> Reporter: Andrew Johnson
> Attachments: DerbyBug.java
>
>
> After at least 65638 updates to an indexed column have been done via an
> updateable holdable resultset and the transaction is committed
> ResultSet.next() returns false even if more rows exist to be returned.
> The following program should output "Total: 100000" but instead outputs
> "Total: 65638".
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> public class DerbyBug {
> public static void main(String[] args) throws ClassNotFoundException,
> SQLException {
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
> Connection conn =
> DriverManager.getConnection("jdbc:derby:TestDB;create=true");
> conn.setAutoCommit(false);
>
> Statement createStmt = conn.createStatement();
> createStmt.executeUpdate("CREATE TABLE test (a INT)");
> createStmt.executeUpdate("CREATE INDEX idxa ON test(a)");
> createStmt.close();
>
> PreparedStatement insertStmt = conn.prepareStatement("INSERT
> INTO test(a) VALUES (?)");
>
> for (int i = 0; i < 100000; ++i) {
> insertStmt.setInt(1, i);
> insertStmt.executeUpdate();
> }
> insertStmt.close();
>
> conn.commit();
>
> Statement selectStmt =
> conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE,
> ResultSet.HOLD_CURSORS_OVER_COMMIT);
> ResultSet rs = selectStmt.executeQuery("SELECT a FROM test FOR
> UPDATE");
>
> int count = 0;
> while (rs.next()) {
> rs.updateInt(1, count);
> rs.updateRow();
> count++;
>
> conn.commit();
> }
>
> rs.close();
> selectStmt.close();
> conn.commit();
> conn.close();
> System.out.println("Total: " + count);
>
> try {
>
> DriverManager.getConnection("jdbc:derby:;shutdown=true");
> } catch (SQLException e) {
> if (!e.getSQLState().equals("XJ015")) {
> throw e;
> }
> }
> }
> }
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
