HQLDB 2.0 and setFetchSize causes a hang

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

HQLDB 2.0 and setFetchSize causes a hang

Geoff Watters
Recently I have noticed that the use of Statement.setFetchSize() can cause a hang when using HSQLDB 2.0. This hang occurs if the number of rows being read from the source is greater than a non-zero FetchSize. This problem does not reproduce when using HSQL Db 2.3.1.

I am not sure if anyone else has seen this problem.

The thread stack when the hang occurs is as follows:-

"main" prio=10 tid=0x000000001d45e800 nid=0xfb4 runnable [0x0000000040931000]
   java.lang.Thread.State: RUNNABLE
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at java.io.BufferedInputStream.fill(BufferedInputStream.java:235)
    at java.io.BufferedInputStream.read(BufferedInputStream.java:254)
    - locked <0x00000007af818d20> (a java.io.BufferedInputStream)
    at java.io.DataInputStream.readByte(DataInputStream.java:265)
    at org.hsqldb.result.Result.newResult(Unknown Source)
    at org.hsqldb.ClientConnection.read(Unknown Source)
    at org.hsqldb.ClientConnection.execute(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.ClientConnection.getRows(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.navigator.RowSetNavigatorClient.getBlock(Unknown Source)
    at org.hsqldb.navigator.RowSetNavigatorClient.getCurrent(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getCurrent(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getLong(Unknown Source)
    at HsqlFetchTest.testFetchSize(HsqlFetchTest.java:33)
    at HsqlFetchTest.main(HsqlFetchTest.java:64)


A Test case that I have been using is shown below:-
//  javac -classpath .:./hsqldb.jar src/HsqlFetchTest.java
//  java -classpath ./src:./hsqldb.jar HsqlFetchTest

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 HsqlFetchTest {

    private static void createData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("CREATE TABLE TEST1(ID INTEGER)");
        }
        catch (SQLException ex) {
            System.out.println("Error creating table "+ex);
        }
        finally {
            stmt.close();
        }

        PreparedStatement preStmt=conn.prepareStatement("INSERT INTO TEST1 VALUES(?)");
        for (int i=0; i<25; i++)
        {
            preStmt.setInt(1, i);
            preStmt.execute();
        }
        conn.commit();
        preStmt.close();
        conn.close();
    }

    private static void dropData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("DROP TABLE TEST1");
        }
        catch (SQLException ex) {
            System.out.println("Error dropping table "+ex);
        }
        finally {
            stmt.close();
        }
        conn.close();
    }

    private static Connection getConnection() throws SQLException
    {
        new org.hsqldb.jdbc.JDBCDriver();
        String url="jdbc:hsqldb:hsql://localhost/testdb";
        String user="SA";
        String pwd=null;

        return DriverManager.getConnection(url,user,pwd);
    }

    private static void testFetchSize(int pFetch) {
        System.out.println("Test with fetchsize="+pFetch);

        String stmtText="select ID from    TEST1 where    (1=1)";
        Connection hsqlConn = null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
            hsqlConn = getConnection();
            stmt = hsqlConn.createStatement();
            if (pFetch>0) {
                System.out.println("Set fetch size to "+pFetch);
                stmt.setFetchSize(pFetch);
            }

            rs = stmt.executeQuery(stmtText);
            int nRows=0;
            while (rs.next()) {
                System.out.println("fetch="+pFetch+" rows="+nRows);
                long age = rs.getLong(1);
                nRows++;
            }

        }
        catch (Exception ex) {
            System.out.println(""+ex);
            ex.printStackTrace();
        }
        finally {
                try {
                    if (rs!=null)
                        rs.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (stmt!=null)
                        stmt.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (hsqlConn!=null)
                        hsqlConn.close();
                }
                catch (SQLException ex) {/* ignore */}
        }
    }

    public static void main(String pArgv[]) {
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }
        try {
            createData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }

        testFetchSize(0);
        testFetchSize(40);
        testFetchSize(10);
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }

    }

}


------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: HQLDB 2.0 and setFetchSize causes a hang

Fred Toussi-2
A lot of issues with earlier versions have been fixed in later versions.
 
Your test case looks fine, therefore it must be a bug in version 2.0 that has been fixed.
 
If you require support or bug fixes for an old version of the software, you can join SupportWare at the appropriate level and sponsor a special release.
 
Fred Toussi
 
On Thu, Feb 6, 2014, at 15:34, Geoff Watters wrote:
Recently I have noticed that the use of Statement.setFetchSize() can cause a hang when using HSQLDB 2.0. This hang occurs if the number of rows being read from the source is greater than a non-zero FetchSize. This problem does not reproduce when using HSQL Db 2.3.1.

I am not sure if anyone else has seen this problem.

The thread stack when the hang occurs is as follows:-

"main" prio=10 tid=0x000000001d45e800 nid=0xfb4 runnable [0x0000000040931000]
   java.lang.Thread.State: RUNNABLE
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:152)
    at java.net.SocketInputStream.read(SocketInputStream.java:122)
    at java.io.BufferedInputStream.fill(BufferedInputStream.java:235)
    at java.io.BufferedInputStream.read(BufferedInputStream.java:254)
    - locked <0x00000007af818d20> (a java.io.BufferedInputStream)
    at java.io.DataInputStream.readByte(DataInputStream.java:265)
    at org.hsqldb.result.Result.newResult(Unknown Source)
    at org.hsqldb.ClientConnection.read(Unknown Source)
    at org.hsqldb.ClientConnection.execute(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.ClientConnection.getRows(Unknown Source)
    - locked <0x00000007af817148> (a org.hsqldb.ClientConnection)
    at org.hsqldb.navigator.RowSetNavigatorClient.getBlock(Unknown Source)
    at org.hsqldb.navigator.RowSetNavigatorClient.getCurrent(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getCurrent(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getLong(Unknown Source)
    at HsqlFetchTest.testFetchSize(HsqlFetchTest.java:33)
    at HsqlFetchTest.main(HsqlFetchTest.java:64)

A Test case that I have been using is shown below:-
//  javac -classpath .:./hsqldb.jar src/HsqlFetchTest.java
//  java -classpath ./src:./hsqldb.jar HsqlFetchTest

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 HsqlFetchTest {

    private static void createData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("CREATE TABLE TEST1(ID INTEGER)");
        }
        catch (SQLException ex) {
            System.out.println("Error creating table "+ex);
        }
        finally {
            stmt.close();
        }

        PreparedStatement preStmt=conn.prepareStatement("INSERT INTO TEST1 VALUES(?)");
        for (int i=0; i<25; i++)
        {
            preStmt.setInt(1, i);
            preStmt.execute();
        }
        conn.commit();
        preStmt.close();
        conn.close();
    }

    private static void dropData() throws SQLException
    {
        Connection conn = getConnection();
        Statement stmt=conn.createStatement();
        try {
            stmt.execute("DROP TABLE TEST1");
        }
        catch (SQLException ex) {
            System.out.println("Error dropping table "+ex);
        }
        finally {
            stmt.close();
        }
        conn.close();
    }

    private static Connection getConnection() throws SQLException
    {
        new org.hsqldb.jdbc.JDBCDriver();
        String url="jdbc:hsqldb:hsql://localhost/testdb";
        String user="SA";
        String pwd=null;

        return DriverManager.getConnection(url,user,pwd);
    }

    private static void testFetchSize(int pFetch) {
        System.out.println("Test with fetchsize="+pFetch);

        String stmtText="select ID from    TEST1 where    (1=1)";
        Connection hsqlConn = null;
        Statement stmt=null;
        ResultSet rs=null;
        try {
            hsqlConn = getConnection();
            stmt = hsqlConn.createStatement();
            if (pFetch>0) {
                System.out.println("Set fetch size to "+pFetch);
                stmt.setFetchSize(pFetch);
            }

            rs = stmt.executeQuery(stmtText);
            int nRows=0;
            while (rs.next()) {
                System.out.println("fetch="+pFetch+" rows="+nRows);
                long age = rs.getLong(1);
                nRows++;
            }

        }
        catch (Exception ex) {
            System.out.println(""+ex);
            ex.printStackTrace();
        }
        finally {
                try {
                    if (rs!=null)
                        rs.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (stmt!=null)
                        stmt.close();
                }
                catch (SQLException ex) {/* ignore */}
                try {
                    if (hsqlConn!=null)
                        hsqlConn.close();
                }
                catch (SQLException ex) {/* ignore */}
        }
    }

    public static void main(String pArgv[]) {
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }
        try {
            createData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }

        testFetchSize(0);
        testFetchSize(40);
        testFetchSize(10);
        try {
            dropData();
        }
        catch (SQLException ex) {
            System.out.println(""+ex);
        }

    }

}
 
------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Managing the Performance of Cloud-Based Applications
Take advantage of what the Cloud has to offer - Avoid Common Pitfalls.
Read the Whitepaper.
http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user