Out of time exception when calling next on ResultSet in Java

Question

I'm working with database, and I'm selecting from it some stuff, like I did thousand times. But this time, the program just freezes when calling resultSet.next(), and after a while throws out of time exception. Any ideas, where could I have mistake? Here's my code:

 try {
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        query = connection.prepareStatement(
                "SELECT * FROM INGREDIENTS WHERE RECIPEID = ?");

        query.setLong(1, recipe.getId());

        ResultSet resultsDB = query.executeQuery();

        SortedSet<Ingredient> result = new TreeSet<Ingredient>();
        while (resultsDB.next()) {
            Ingredient output = rowToIngredient(resultsDB);
            validate(output);

            result.add(output);
        }
        connection.commit();
        return result;
     }

The Program freezes exactly at resultsDB.next() when it is called as condition in while cycle. When this part of code is executed 1st time, it works, when the program reaches there second time with another data, .next() method freezes the program. The program never gets into the cycle, I used breakpoints and it fails on the line where the condition is evalueated. Thanks for advices

This whole code is in for cycle, is it OK? As far as I am using data source, it should be

Error:

java.sql.SQLTransactionRollbackException: A lock could not be obtained within the time requested
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at fi.muni.pv168.RecipebookImpl.getIngredientsOfRecipe(RecipebookImpl.java:53)
    at fi.muni.pv168.RecipebookImpl.isIngredientInRecipe(RecipebookImpl.java:324)
    at fi.muni.pv168.RecipebookImpl.addIngredientsToRecipe(RecipebookImpl.java:92)
    at fi.muni.pv168.RecipebookImplTest.testFindRecipesByIngredients(RecipebookImplTest.java:390)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:53)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:123)
    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:104)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
    at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
    at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:107)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)
Caused by: java.sql.SQLException: A lock could not be obtained within the time requested
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 46 more

and there are few more

Answer 1

You must have another concurrent non-committed transaction which is updating a row that you're trying to read. This causes the row to be locked, and your transaction to wait until this lock is released.

If you're using a tool to execute requests on the database, make sure to commit or rollback the current transaction in this tool. If you have another application, or another thread in the same application using the same database, check if it doesn't have long-running transactions.