db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5425) Updateable holdable ResultSet terminates early after 65638 updates
Date Wed, 28 Sep 2011 16:58:45 GMT

    [ 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

        

Mime
View raw message