[jira] [Updated] (DERBY-5425) Updateable holdable ResultSet terminates early after 65638 updates

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-5425?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-5425:
--------------------------------------

    Attachment: d5425-1a.diff.txt

I'm uploading a patch (d5425-1a.diff.txt) that makes updatable cursors use BackingStoreHashtable to keep track of the rows that have been moved in the scan direction and therefore need to be ignored the next time they are seen. BackingStoreHashtable has a flag that specifies whether its contents should be kept over commit, which makes it possible to get around the problems with the original code.

In essence, the patch just makes UpdateResultSet.notifyForUpdateCursor() create a BackingStoreHashtable instead of a java.util.Hashtable, and removes all the code that handles overflow to a TemporaryRowHolder instance as that's now handled transparently by BackingStoreHashtable.

One existing test case had to be modified to pass with the patch. UpdateCursorTest.testVirtualMemoryHeap() expected a particular ordering of the rows. Specifically, it expected the rows stored in the in-memory portion of the TemporaryRowHolder to come out in reversed order. Since we no longer use a TemporaryRowHolder to store the overflow rows when the patch is applied, the rows returned from the index scan will follow the index ordering also when the hash table overflows. I would think the new ordering is more in line with the users' expectation of how results from an index scan are ordered.

All the regression tests ran cleanly with the patch.

More detailed description of the changes:

* impl/sql/execute/UpdateResultSet.java

Create BackingStoreHashtable instead of java.util.Hashtable.

  - Initialize BackingStoreHashtable.keepAfterCommit based on the holdability of the underlying scan.

  - Use same initial capacity and maximum capacity as the original hash table, so that the memory footprint will be roughly the same.

Remove code that handles the hash table growing beyond maxCapacity, as that's handled internally in BackingStoreHashtable now by spilling to disk.

* impl/sql/execute/TableScanResultSet.java

Remove code that reads the overflow rows, as the distinction between rows in memory and rows spilt to disk .

Remove fields that are only used to handle overflow rows, and code that checks the values of those fields.

Make close() release the resources held by the BackingStoreHashtable.

* impl/sql/execute/IndexRowToBaseRowResultSet.java

Remove fields that are only used to handle overflow rows

* impl/sql/execute/CurrentOfResultSet.java

Remove code that reads the overflow rows, and the fields that are only used in the removed code.

* functionTests/tests/lang/UpdateCursorTest.java

Update expected ordering of rows in testVirtualMemoryHeap() as described above.

Add test case testDerby5425HoldOverCommit() which exposes this bug and verifies the fix. Since UpdateCursorTest sets derby.language.maxMemoryPerTable to 1, the bug can be exposed with a much smaller number of rows than in the original repro.
                
> 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
>            Assignee: Knut Anders Hatlen
>              Labels: derby_triage10_9
>         Attachments: DerbyBug.java, d5425-1a.diff.txt, holdable-row-holders.diff.txt
>
>
> 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