Cannot read table while writing transaction in another connection

[Al Eridani <al...@gmail.com>]

The code below shows a problem I'm having with Derby while trying to
count the rows in a table when
another connection is writing to the database and it has not been committed yet.

After 60 seconds of delay, It throws

java.sql.SQLTransactionRollbackException: A lock could not be obtained
within the time requested
	at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
	at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.executeQuery(Unknown Source)
	at org.lockss.exporter.counter.TestDerby.checkTitleRowCount(TestDerby.java:175)
	at org.lockss.exporter.counter.TestDerby.test2(TestDerby.java:155)
	at org.lockss.exporter.counter.TestDerby.main(TestDerby.java:33)
Caused by: org.apache.derby.client.am.SqlException: A lock could not
be obtained within the time requested
	at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
	at org.apache.derby.client.net.NetStatementReply.parseOpenQueryError(Unknown
Source)
	at org.apache.derby.client.net.NetStatementReply.parseOPNQRYreply(Unknown
Source)
	at org.apache.derby.client.net.NetStatementReply.readOpenQuery(Unknown Source)
	at org.apache.derby.client.net.StatementReply.readOpenQuery(Unknown Source)
	at org.apache.derby.client.net.NetStatement.readOpenQuery_(Unknown Source)
	at org.apache.derby.client.am.Statement.readOpenQuery(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.executeQueryX(Unknown Source)
	... 4 more

I would have expected that around line 175 I would get the row count
as it was before the uncommitted
transaction started and that no lock would be needed to just read.

Is this the way it is supposed to work? If so, is there some
configuration parameters that I can set up
to get the behavior I expected?

It is trivial to fix this simple example created to illustrate the
situation (test1 works) but we have some
long-running transactions that totally block read access to other
parts of our application.

Thanks!

=====================================================================
package test;

import java.net.InetAddress;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.derby.drda.NetworkServerControl;
import org.apache.derby.jdbc.ClientDataSource;

public class TestDerby {
  private static final String TABLE_CREATE =
      "create table titles (title_id bigint NOT NULL PRIMARY
KEY,title_name varchar(512) NOT NULL)";

  private static final String TITLE_INSERT =
      "insert into titles (title_id,title_name) values (?,?)";

  private static final String TITLE_COUNT = "select count(*) from titles";

  private static final String TITLE_DROP = "drop table titles";

  DataSource dataSource = null;

  public static void main(String[] args) {
    try {
      TestDerby app = new TestDerby();
      app.dbSetUp();

      // app.test1();
      app.test2();
    } catch (Exception e) {
      System.err.println("Exception caught: " + e);
      e.printStackTrace();
    }
  }

  public void dbSetUp() throws Exception {
    String dataSourceClassName = "org.apache.derby.jdbc.ClientDataSource";
    Class<?> dataSourceClass;

    try {
      dataSourceClass = Class.forName(dataSourceClassName);
    } catch (Throwable t) {
      throw new Exception("Cannot locate datasource class '"
	  + dataSourceClassName + "'", t);
    }

    try {
      dataSource = ((DataSource) dataSourceClass.newInstance());
    } catch (ClassCastException cce) {
      throw new Exception("Class '" + dataSourceClassName
	  + "' is not a DataSource.", cce);
    } catch (Throwable t) {
      throw new Exception("Cannot create instance of datasource class '"
	  + dataSourceClassName + "'", t);
    }

    try {
      BeanUtils.setProperty(dataSource, "user", "dbuser");
      BeanUtils.setProperty(dataSource, "rootDir", "/tmp/TestDerby");
      BeanUtils.setProperty(dataSource, "portNumber", "1527");
      BeanUtils.setProperty(dataSource, "databaseName", "/tmp/TestDerby/db");
      BeanUtils.setProperty(dataSource, "createDatabase", "create");
      BeanUtils.setProperty(dataSource, "serverName", "localhost");
    } catch (Throwable t) {
      throw new Exception("Cannot configure datasource", t);
    }

    if (dataSource instanceof ClientDataSource) {
      if (!startNetworkServerControl(dataSource)) {
	throw new Exception("Cannot start NSC");
      }
    }

    Connection conn = null;
    Statement statement = null;

    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      statement = conn.createStatement();
      statement.execute(TABLE_CREATE);
    } finally {
      statement.close();
      conn.commit();
      conn.close();
    }
  }

  private boolean startNetworkServerControl(DataSource dataSource)
      throws Exception {
    ClientDataSource cds = (ClientDataSource) dataSource;
    String serverName = cds.getServerName();
    int serverPort = cds.getPortNumber();

    InetAddress inetAddr = InetAddress.getByName(serverName);
    NetworkServerControl networkServerControl =
	new NetworkServerControl(inetAddr, serverPort);
    networkServerControl.start(null);

    for (int i = 0; i < 40; i++) {
      try {
	networkServerControl.ping();
	return true;
      } catch (Exception e) {
	try {
	  Thread.sleep(500);
	} catch (InterruptedException ie) {
	  break;
	}
      }
    }

    return false;
  }

  // This works.
  public void test1() throws SQLException {
    Connection conn = null;
    PreparedStatement insertTitle = null;
    int count = -1;

    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      insertTitle = conn.prepareStatement(TITLE_INSERT);
      insertTitle.setLong(1, 123456L);
      insertTitle.setString(2, "TestTitle");
      count = insertTitle.executeUpdate();
    } finally {
      insertTitle.close();
      conn.commit();
      conn.close();
    }

    checkTitleRowCount(count);
    dropTitle();
  }

  // This fails.
  public void test2() throws SQLException {
    Connection conn = null;
    PreparedStatement insertTitle = null;
    int count = -1;

    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      insertTitle = conn.prepareStatement(TITLE_INSERT);
      insertTitle.setLong(1, 123456L);
      insertTitle.setString(2, "TestTitle");
      count = insertTitle.executeUpdate();

      checkTitleRowCount(count);
    } finally {
      insertTitle.close();
      conn.commit();
      conn.close();
    }

    dropTitle();
  }

  private void checkTitleRowCount(int expected) throws SQLException {
    Connection conn = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    int count = -1;

    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      statement = conn.prepareStatement(TITLE_COUNT);
      resultSet = statement.executeQuery(); // <-- It fails here.

      if (resultSet.next()) {
	count = resultSet.getInt(1);
      }
    } finally {
      if (resultSet != null)
	resultSet.close();
      statement.close();
      conn.rollback();
      conn.close();
    }

    if (expected != count) {
      throw new RuntimeException("Failed check.");
    }
  }

  private void dropTitle() throws SQLException {
    Connection conn = null;
    Statement statement = null;

    try {
      conn = dataSource.getConnection();
      conn.setAutoCommit(false);
      statement = conn.createStatement();
      statement.execute(TITLE_DROP);
    } finally {
      statement.close();
      conn.commit();
      conn.close();
    }
  }
}

Re: Cannot read table while writing transaction in another connection

[Al Eridani <al...@gmail.com>]

On Mon, Jul 30, 2012 at 5:27 PM, Bryan Pendleton
<bp...@gmail.com> wrote:

> You can try using a lower isolation level:
> http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts15366.html
>
> But that's not exactly the same thing (e.g., at a lower isolation level,
> it might be that the count returns the instantaneous count of rows
> in the table, including some rows from uncommitted transactions).

Thank  you, Bryan.

Re: Cannot read table while writing transaction in another connection

[Bryan Pendleton <bp...@gmail.com>]

> I would have expected that around line 175 I would get the row count
> as it was before the uncommitted
> transaction started and that no lock would be needed to just read.

Unfortunately, Derby doesn't currently implement these "snapshot isolation"
types of semantics.

> Is this the way it is supposed to work? If so, is there some
> configuration parameters that I can set up
> to get the behavior I expected?

You can try using a lower isolation level:
http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts15366.html

But that's not exactly the same thing (e.g., at a lower isolation level,
it might be that the count returns the instantaneous count of rows
in the table, including some rows from uncommitted transactions).

thanks,

bryan