Java Code Examples for java.sql.Connection#setTransactionIsolation()

The following examples show how to use java.sql.Connection#setTransactionIsolation() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Tests that the effects of executing a stored procedure with
 * <code>executeUpdate()</code> are correctly rolled back when the
 * query fails because the stored procedure returned a result set.
 *
 * <p> This test case fails with JCC.
 *
 * @exception SQLException if a database error occurs
 */
public void testRollbackStoredProcWhenExecuteUpdateReturnsResults()
    throws SQLException
{
    Connection conn = getConnection();
    // GemsStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    conn.setAutoCommit(true);
    Statement stmt = createStatement();
    try {
        stmt.executeUpdate("CALL PROC_WITH_SIDE_EFFECTS(1)");
        fail("executeUpdate() didn't fail.");
    } catch (SQLException sqle) {
        assertResultsFromExecuteUpdate(sqle);
    }
    // Expect Side effects from stored procedure to be rolled back.
    JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));

}
 
Example 2
Source Project: gemfirexd-oss   File: GFXDUtil.java    License: Apache License 2.0 6 votes vote down vote up
public static  Connection openConnection(String driver, String url,
    int txIsolation, boolean autocommit, Properties p) throws SQLException {
  Log.getLogWriter().info("Creating connection using " + driver 
     + " with " + url + " and properties: " + p); 
  loadDriver(driver);
  Connection conn;
  if (p == null) {
    conn = DriverManager.getConnection(url);
  } else {
    conn = DriverManager.getConnection(url, p);
  }
  conn.setAutoCommit(autocommit);
  conn.setTransactionIsolation(txIsolation);
  Log.getLogWriter().info("Created connection using transaction isolation: "
     + GFXDPrms.getTxIsolation(conn.getTransactionIsolation())
     + " autocommit: " + autocommit);
  
  return conn;
}
 
Example 3
Source Project: gemfirexd-oss   File: TransactionDUnit.java    License: Apache License 2.0 6 votes vote down vote up
public void testBug42067_2() throws Exception {

    // Create the controller VM as client which belongs to default server group
    startClientVMs(1, 0, null);
    startServerVMs(2, -1, "SG1");
    Connection conn = TestUtil.jdbcConn;
    conn.setTransactionIsolation(getIsolationLevel());
    conn.setAutoCommit(false);
    // create table
    clientSQLExecute(1, "Create table t1 (c1 int not null primary key, "
        + "c2 int not null, c3 int not null, c4 int not null) "
        + "redundancy 1 partition by column (c1) "+ getSuffix());
    conn.commit();
    Statement st = conn.createStatement();
    st.execute("insert into t1 values (1, 1,1,1)");
    st.execute("insert into t1 values (114, 114,114,114)");
    conn.commit();
    st.execute("delete from t1 where c1 =1 and c3 =1");
    st.execute("update t1 set c2 =2 where c1 =1 and c3 =1");
    conn.commit();
    ResultSet rs = st.executeQuery("select * from t1");
    assertTrue(rs.next());
    assertEquals(114, rs.getInt(1));
    assertFalse(rs.next());
  }
 
Example 4
Source Project: gemfirexd-oss   File: ProcedureTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Tests that the effects of executing a stored procedure with
 * <code>executeQuery()</code> are correctly rolled back when the
 * query fails because the number of returned result sets is zero.
 *
 * <p> This test case fails with JCC.
 *
 * @exception SQLException if a database error occurs
 */
public void testRollbackStoredProcWhenExecuteQueryReturnsNothing_prepared()
    throws SQLException
{
    Connection conn = getConnection();
    conn.setAutoCommit(true);
    // GemsStone changes BEGIN
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // GemStone changes END
    PreparedStatement ps =
        prepareStatement("CALL PROC_WITH_SIDE_EFFECTS(?)");
    ps.setInt(1, 0);
    try {
        ps.executeQuery();
        fail("executeQuery() didn't fail.");
    } catch (SQLException sqle) {
        assertNoResultSetFromExecuteQuery(sqle);
    }
    Statement stmt = createStatement();
    // Expect Side effects from stored procedure to be rolled back.
    JDBC.assertEmpty(stmt.executeQuery("SELECT * FROM SIMPLE_TABLE"));

}
 
Example 5
private Connection openConnection(String username, String password) throws SQLException {
  Connection connection;
  try {
    connection = DriverManager.getConnection(AbstractConnectionResources.this.getJdbcUrl(), username, password);
  } catch (SQLException se) {
    log.error(String.format("Unable to connect to URL: %s, with user: %s", AbstractConnectionResources.this.getJdbcUrl(), username));
    throw se;
  }
  try {
    // Attempt to switch to read-committed. This is the default on many platforms, but not on MySQL.
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    return connection;
  } catch (Exception e) {
    connection.close();
    throw e;
  }
}
 
Example 6
Source Project: gemfirexd-oss   File: TransactionTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testTxnDeleteParentRow() throws Exception {
  Connection conn = getConnection();
  Statement stmtp1 = conn.createStatement();
  stmtp1.execute("Create table t1 (c1 int not null primary key, "
      + "c2 int not null, c3 int not null, "
      + "constraint C3_Unique unique (c3))"+getSuffix());

  stmtp1.execute("Create table t2 (c1 int not null primary key, "
      + "c2 int not null, c3 int not null, "
      + "foreign key (c1) references t1(c1))"+getSuffix());

  stmtp1.execute("insert into t1 values(1, 1, 1)");
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  Statement stmtp2 = conn.createStatement();
  stmtp2.execute("delete from t1 where c1 = 1");

  Connection childConn = TestUtil.getConnection();
  childConn.setTransactionIsolation(getIsolationLevel());
  Statement childStmnt = childConn.createStatement();
  addExpectedException(ConflictException.class);
  try {
    childStmnt.execute("insert into t2 values(1, 1, 1)");
    fail("expected conflict exception");
  } catch (SQLException sqle) {
    if (!"X0Z02".equals(sqle.getSQLState())) {
      throw sqle;
    }
  }
  removeExpectedException(ConflictException.class);

  // after rollback of parent, there should be no conflict
  conn.rollback();
  childStmnt.execute("insert into t2 values(1, 1, 1)");
  childConn.commit();
}
 
Example 7
public static Runnable prepInsert() {
  CacheSerializableRunnable senderConf = new CacheSerializableRunnable(
      "Sender Configurator") {
    @Override
    public void run2() throws CacheException {
      try {
        Connection conn = TestUtil.jdbcConn;
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        PreparedStatement prep = conn.prepareStatement("insert into "
            + "EMP.PARTITIONED_TABLE (ID, DESCRIPTION, ADDRESS, COMPANY) values (?, ?, ?, ?)");
        prep.setInt(1,1);
        prep.setString(2, "First");
        prep.setString(3, "A714");
        prep.setString(4, "Pivotal");
        prep.addBatch();
        prep.setInt(1,2);
        prep.setString(2, "Second");
        prep.setString(3, "J 605");
        prep.setString(4, "Zimbra");
        prep.addBatch();
        prep.executeBatch();
        conn.commit();
      } catch (SQLException sqle) {
        throw GemFireXDRuntimeException.newRuntimeException(null, sqle);
      }
    }
  };
  return senderConf;
}
 
Example 8
/**
 * Tests that a ResultSet opened even in read uncommitted, gets a 
 * table intent lock, and that another transaction then cannot compress 
 * the table while the ResultSet is open.
 **/
public void testTableIntentLock1()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    println("T1: select * from t1");
    ResultSet rs = s.executeQuery("select * from t1 for update");
    while (rs.next()) {
        println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    } // Now the cursor does not point to any tuples
    
    // Compressing the table in another transaction:
    Connection con2 = openDefaultConnection();
    
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    PreparedStatement ps2 = con2.prepareStatement
        ("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
    ps2.setString(1, "SPLICE");
    ps2.setString(2, "T1");
    ps2.setInt(3, 0);
    println("T2: call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(SPLICE, T1, 0)");
    try {
        ps2.executeUpdate(); // This will hang
        fail("Expected T2 to hang");
    } catch (SQLException e) {
        println("T2: Got exception:" + e.getMessage());
        
        assertSQLState(LOCK_TIMEOUT_EXPRESSION_SQL_STATE, e);

    }
    ps2.close();
    con2.rollback();
    con2.close();
    
    s.close();
}
 
Example 9
Source Project: gemfirexd-oss   File: TransactionTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testBug41168() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement stmt = conn.createStatement();

  conn.setTransactionIsolation(getIsolationLevel());

  // Create the table with self-reference FKs
  stmt.execute("create table BinaryTree (id int primary key, "
      + "leftId int, rightId int, depth int not null,"
      + " foreign key (leftId) references BinaryTree(id),"
      + " foreign key (rightId) references BinaryTree(id)) replicate"+getSuffix());

  addExpectedException(SQLIntegrityConstraintViolationException.class);
  doBinaryTreeChecks(conn, true);
  removeExpectedException(SQLIntegrityConstraintViolationException.class);

  // now do the same for partitioned table
  stmt.execute("drop table BinaryTree");
  this.waitTillAllClear();
  stmt.execute("create table BinaryTree (id int primary key, "
      + "leftId int, rightId int, depth int not null,"
      + " foreign key (leftId) references BinaryTree(id),"
      + " foreign key (rightId) references BinaryTree(id))"+getSuffix());

  addExpectedException(SQLIntegrityConstraintViolationException.class);
  doBinaryTreeChecks(conn, true);
  removeExpectedException(SQLIntegrityConstraintViolationException.class);
}
 
Example 10
Source Project: ZhihuSpider   File: UnpooledDataSource.java    License: MIT License 5 votes vote down vote up
private void configureConnection(Connection conn) throws SQLException {
	if (autoCommit != conn.getAutoCommit()) {
		conn.setAutoCommit(autoCommit);
	}
	if (defaultTransactionIsolationLevel != null) {
		conn.setTransactionIsolation(defaultTransactionIsolationLevel);
	}
}
 
Example 11
Source Project: gemfirexd-oss   File: TransactionTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testTransactionalInsertOnReplicatedTable() throws Exception {
  Connection conn= getConnection();
  Statement st = conn.createStatement();
  st.execute("Create table t1 (c1 int not null , c2 int not null, "
      + "primary key(c1)) replicate"+getSuffix());
  conn.commit();
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  st.execute("insert into t1 values (10, 10)");

  conn.rollback();// rollback.

  ResultSet rs = st.executeQuery("Select * from t1");
  assertFalse("ResultSet should be empty ", rs.next());
  rs.close();

  st.execute("insert into t1 values (10, 10)");
  st.execute("insert into t1 values (20, 20)");

  conn.commit(); // commit two rows.
  rs = st.executeQuery("Select * from t1");
  int numRows = 0;
  while (rs.next()) {
    // Checking number of rows returned, since ordering of results
    // is not guaranteed. We can write an order by query for this (another
    // test).
    numRows++;
  }
  assertEquals("ResultSet should contain two rows ", 2, numRows);

  // Close connection, resultset etc...
  rs.close();
  st.close();
  conn.commit();
  conn.close();
}
 
Example 12
public void testRollbackBecauseOfDbTimeout() throws SQLException
{
    int orderId = 1;
    int newUserId = -999999;
    String description = "new long description";
    Order order = OrderFinder.findOne(OrderFinder.orderId().eq(orderId));
    int oldUserId = order.getUserId();
    String oldDescription = order.getDescription();

    Connection con = this.getConnection();
    con.setAutoCommit(false);
    con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    String sql = "select * from APP.ORDERS for UPDATE";
    PreparedStatement ps = con.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    rs.next();
    try
    {
        try
        {
            order.setUserIdAndDescription(newUserId, description);
            fail("commit should have failed");
        }
        catch (MithraDatabaseException e)
        {
            //must roll back because we have shutdown the db
        }
        assertEquals(oldUserId, order.getUserId());
        assertEquals(oldDescription, order.getDescription());
    }
    finally
    {
        ps.close();
        if (con != null)
        {
            con.close();
        }
    }
}
 
Example 13
Source Project: gemfirexd-oss   File: TransactionDUnit.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Test updates on tables partitioned by PK.
 * 
 * @throws Exception
 */
public void testTransactionalKeyBasedUpdatePartitionedByPk() throws Exception {
  startServerVMs(2, 0, "sg1");
  startClientVMs(1, 0, null);
  Connection conn = TestUtil.jdbcConn;
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  st.execute("create schema test default server groups (sg1, sg2)");
  st.execute("create table test.t1 ( PkCol1 int not null, PkCol2 int not null , "
      + "col3 int, col4 int, col5 varchar(10), Primary Key (PkCol1) ) "
      + "Partition by Primary Key server groups (sg1) redundancy 1"+ getSuffix());

  conn.setTransactionIsolation(getIsolationLevel());
  PreparedStatement psInsert = conn.prepareStatement("insert into test.t1 "
      + "values(?, 10, 10, 10, 'XXXX1')");
  // st.execute("insert into test.t1 values(10, 10, 10, 10, 'XXXX1')");
  for (int i = 0; i < 1000; i++) {
    psInsert.setInt(1, i);
    psInsert.executeUpdate();
    conn.commit();
  }
  ResultSet rs = st.executeQuery("select * from test.t1");
  int numRows = 0;
  while (rs.next()) {
    assertEquals("Column value should be 10", 10, rs.getInt(3));
    assertEquals("Column value should be 10", 10, rs.getInt(4));
    assertEquals("Column value should be XXXX1", "XXXX1", rs.getString(5)
        .trim());
    numRows++;
  }
  assertEquals("Numbers of rows in resultset should be one", 1000, numRows);

  PreparedStatement psUpdate = conn.prepareStatement("update test.t1 set "
      + "col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=?");
  // st.execute("update test.t1 set col3 = 20, col4 = 20, col5 = 'changed' where PkCol1=10");
  for (int i = 0; i < 1000; i++) {
    psUpdate.setInt(1, i);
    psUpdate.executeUpdate();
    conn.commit();
  }

  rs = st.executeQuery("select * from test.t1");
  numRows = 0;
  while (rs.next()) {
    assertEquals("Column value should change", 20, rs.getInt(3));
    assertEquals("Columns value should change", 20, rs.getInt(4));
    assertEquals("Columns value should change", "changed", rs.getString(5)
        .trim());
    numRows++;
  }
  assertEquals("Numbers of rows in resultset should be one", 1000, numRows);
  rs.close();
  st.close();
  conn.commit();
  conn.close();

}
 
Example 14
/**
 * Test what happens if you update a deleted + purged tuple.
 * The transaction which deletes the tuple, will also
 * ensure that the tuple is purged from the table, not only marked
 * as deleted.
 **/
public void testUpdatePurgedTuple1()
    throws SQLException
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next();
    int firstKey = rs.getInt(1);
    println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    int lastKey = firstKey;
    while (rs.next()) {
        lastKey = rs.getInt(1);
        println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
    }
    
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=? or id=?");
        ps2.setInt(1, firstKey);
        ps2.setInt(2, lastKey);
        assertEquals("Expected two records to be deleted", 
                     2, ps2.executeUpdate());
        println("T2: Deleted records with id=" + firstKey + " and id=" + 
                lastKey);
        con2.commit();
        println("T2: commit");
        ps2 = con2.prepareStatement
            ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
        ps2.setString(1, "SPLICE"); // schema
        ps2.setString(2, "T1");  // table name
        ps2.setInt(3, 1); // purge
        ps2.setInt(4, 0); // defragment rows
        ps2.setInt(5, 0); // truncate end
        println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
        println("T3: purges deleted records");
        ps2.executeUpdate();
        con2.commit();
        println("T3: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    rs.first(); // Go to first tuple
    println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updateRow()");
    rs.last(); // Go to last tuple
    println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
            rs.getInt(2) + "," +
            rs.getInt(3) + ")");
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updateRow()");
    commit();
    println("T1: commit");
    rs = s.executeQuery("select * from t1");
    println("T3: select * from table");
    while (rs.next()) {
        println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
    s.close();
}
 
Example 15
Source Project: gemfirexd-oss   File: StatementHelper.java    License: Apache License 2.0 4 votes vote down vote up
StatementHelper(Connection conn, boolean autoCommit, int isolation) throws SQLException
{
    this.conn = conn;
    conn.setAutoCommit(autoCommit);
    conn.setTransactionIsolation(isolation);
}
 
Example 16
public void testCreateStatsNonTxn() throws Exception {
  try {
    Properties serverInfo = new Properties();
    serverInfo.setProperty("gemfire.enable-time-statistics", "true");
    serverInfo.setProperty("statistic-sample-rate", "100");
    serverInfo.setProperty("statistic-sampling-enabled", "true");
    serverInfo.setProperty("server-groups", "MYGROUP");

    startServerVMs(1, 0, null, serverInfo);

    startServerVMs(1, 0, null, serverInfo);

    Properties info = new Properties();
    info.setProperty("host-data", "false");
    info.setProperty("gemfire.enable-time-statistics", "true");

    // start a client, register the driver.
    startClientVMs(1, 0, null, info);

    // enable StatementStats for all connections in this VM
    System.setProperty(GfxdConstants.GFXD_ENABLE_STATS, "true");

    // check that stats are enabled with System property set
    Connection conn = TestUtil.getConnection(info);
    conn.setTransactionIsolation(Connection.TRANSACTION_NONE);
    conn.setAutoCommit(false);
    createTable(conn);
    insertRow(conn);
    validatePutStats(2);
    conn.close();

    for (VM vm : this.serverVMs) {
      validatePutStats(vm, false, 1);
    }

    stopVMNums(1, -1);
  } finally {
    GemFireXDQueryObserverHolder.clearInstance();
    System.clearProperty(GfxdConstants.GFXD_ENABLE_STATS);
  }
}
 
Example 17
public void _testSelectForUpdateForDebugging() throws Exception {
  Connection conn = getConnection();
  Statement stmtForTableAndInsert = conn.createStatement();
  stmtForTableAndInsert.execute("create table Employee"
      + "(firstname varchar(50) not null, lastname varchar(50) not null, "
      + "workdept varchar(50), bonus decimal(10,4), "
      + "primary key (firstname, lastname))");
  stmtForTableAndInsert
      .execute("insert into employee values('neeraj', 'kumar', 'rnd', 0.0), "
          + "('asif', 'shahid', 'rnd', 1.0), "
          + "('dada', 'ji', 'rnd', 2.0), ('sum', 'wale', 'rnd', 3.0)");
  conn.commit();
  // conn.setAutoCommit(false);
  // Create the statement with concurrency mode CONCUR_UPDATABLE
  // to allow result sets to be updatable
  conn.setTransactionIsolation(getIsolationLevel());
  Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
      ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
  // Statement stmt = conn.createStatement();
  // Updatable statements have some requirements
  // for example, select must be on a single table
  // Only bonus can be updated
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT WORKDEPT, BONUS /*, firstname, LastNaME*/ " +
  // "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");

  // Only bonus can be updated
  ResultSet uprs = stmt.executeQuery("SELECT workdept, bonus "
      + "FROM EMPLOYEE where lastname = 'kumar' FOR UPDATE of BONUS");
  // ResultSet uprs = stmt.executeQuery(
  // "SELECT firstname, count(*) " +
  // "FROM EMPLOYEE group by firstname FOR UPDATE of BONUS"); // Only bonus
  // can be updated

  while (uprs.next()) {
    uprs.getString("WORKDEPT");
    BigDecimal bonus = uprs.getBigDecimal("BONUS");
    // if (workDept.equals(theDept)) {
    if (true) {
      // if the current row meets our criteria,
      // update the updatable column in the row
      uprs.updateBigDecimal("BONUS", bonus.add(BigDecimal.valueOf(250L)));
      // uprs.updateBigDecimal("BONUS", null);
      uprs.updateRow();
      // System.out.println("Updating bonus for employee:" +
      // firstnme +" "+ lastName);
    }
  }
  conn.commit(); // commit the transaction
  // close object
  uprs.close();
  ResultSet rs = stmt.executeQuery("select * from employee");
  while (rs.next()) {
    System.out.println(rs.getString(1) + ", " + rs.getString(2) + ", "
        + rs.getString(3) + ", " + rs.getBigDecimal(4));
  }
  conn.commit();
  stmt.close();
  // Close connection if the application does not need it any more
  conn.close();

}
 
Example 18
Source Project: gemfirexd-oss   File: QueryCancelDUnit.java    License: Apache License 2.0 4 votes vote down vote up
public void deleteQueryTestWithTx(boolean usePrepStatement, boolean useCancelProc,
    boolean useThinClient, String testName) throws Throwable {
  final int numServers = 2;
  int tableSize = 40;

  startVMs(1, numServers);
  Connection cxn = _getConnection(useThinClient);
 
  this.createTables(cxn, tableSize);
  
  cxn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  cxn.setAutoCommit(false);
  
  Statement st = cxn.createStatement();
  st.execute("insert into MyTable values(1, 1)");
  st.execute("insert into MyTable values(2, 2)");
  st.execute("insert into MyTable values(3, 3)");
  st.execute("insert into MyTable values(4, 4)");
  
  ResultSet rs = st.executeQuery("select count(*) from MyTable");
  assertTrue(rs.next());
  int count = rs.getInt(1);
  // 40 committed earlier and 4 added above not yet committed
  assertEquals(44, count);
  
  Statement stmt = null;
  String testQueryString = "delete from MyTable where x > 0";
  if (usePrepStatement) {
    final PreparedStatement pstmt2 = cxn
        .prepareStatement("delete from MyTable where x > ?");
    pstmt2.setInt(1, 0);
    stmt = pstmt2;
  } else {
    stmt = cxn.createStatement();
  }

  final String key = testName;
  CacheSerializableRunnable csr1 = new CacheSerializableRunnable(
      testName) {

    @Override
    public void run2() {
      GemFireXDQueryObserver old = GemFireXDQueryObserverHolder
          .setInstance(new GemFireXDQueryObserverAdapter() {
            private boolean flag = false;

            @Override
            public void onDeleteResultSetOpen(
                com.pivotal.gemfirexd.internal.iapi.sql.ResultSet resultSet) {
              getLogWriter().info("onDeleteResultSetOpen called");
              if (!flag) {
                incrementValueInBBMap(key, numServers);
                Activation a = resultSet.getActivation(); 
                long connId = a.getConnectionID();
                long stmtId = a.getStatementID();
                long execId = a.getExecutionID();
                putStatementUUIDinBBMap(key, connId, stmtId, execId);
                flag = true;
              }
              try {
                Thread.sleep(1000);
              } catch (InterruptedException e) {
              }
            }
          });
    }
  };
  
  // set up the above runnable object
  serverExecute(1, csr1);
  serverExecute(2, csr1);

  // execute the query in a different thread, this will result in 
  // data nodes incrementing a value in BB map in observer
  // callback as an indication to cancel the query
  executeAndCancelQuery(stmt,
      usePrepStatement ? StatementType.PREPARED_STATEMENT
          : StatementType.STATEMENT, 
          numServers, key, testQueryString, useCancelProc);
  

  rs = st.executeQuery("select count(*) from MyTable");
  assertTrue(rs.next());
  count = rs.getInt(1);
  // Tx should have rolled back because of delete query cancellation
  // and so the previously added 4 rows should not show up.
  assertEquals(40, count);

}
 
Example 19
Source Project: gemfirexd-oss   File: ConcurrencyTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Test what happens if you update a tuple which is deleted, purged and
 * reinserted
 **/
public void testUpdatePurgedTuple3()
    throws SQLException 
{
    getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                      ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = s.executeQuery("select * from t1");
    rs.next(); // Point to first tuple
    int firstKey = rs.getInt(1);
    println("T1: read tuple with key " + firstKey);
    rs.next(); // Go to next
    println("T1: read next tuple");
    Connection con2 = openDefaultConnection();
    con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    try {
        PreparedStatement ps2 = con2.prepareStatement
            ("delete from t1 where id=?");
        ps2.setInt(1, firstKey);
        assertEquals("Expected one record to be deleted", 1, 
                     ps2.executeUpdate());
        println("T2: Deleted record with id=" + firstKey);
        con2.commit();
        println("T2: commit");
        
        // Now purge the table
        ps2 = con2.prepareStatement
            ("call SYSCS_UTIL.INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
        ps2.setString(1, "APP"); // schema
        ps2.setString(2, "T1");  // table name
        ps2.setInt(3, 1); // purge
        ps2.setInt(4, 0); // defragment rows
        ps2.setInt(5, 0); // truncate end
        println("T3: call SYSCS_UTIL.INPLACE_COMPRESS_TABLE");
        println("T3: purges deleted records");
        ps2.executeUpdate();
        con2.commit();
        
        ps2 = con2.prepareStatement("insert into t1 values(?,?,?,?)");
        ps2.setInt(1, firstKey);
        ps2.setInt(2, -1);
        ps2.setInt(3, -1);
        ps2.setString(4, "UPDATED TUPLE");
        assertEquals("Expected one record to be inserted", 1, 
                     ps2.executeUpdate());
        println("T4: Inserted record (" + firstKey + ",-1,-1)" );
        con2.commit();
        println("T4: commit");
    } catch (SQLException e) {
        con2.rollback();
        throw e;
    }
    println("T1: read previous tuple");
    rs.previous(); // Go back to first tuple
    println("T1: id=" + rs.getInt(1));
    rs.updateInt(2, 3);
    println("T1: updateInt(2, 3);");
    rs.updateRow();
    println("T1: updated column 2, to value=3");
    println("T1: commit");
    commit();
    rs = s.executeQuery("select * from t1");
    while (rs.next()) {
        println("T5: Read Tuple:(" + rs.getInt(1) + "," +
                rs.getInt(2) + "," +
                rs.getInt(3) + ")");
        
    }
    
    con2.close();
}
 
Example 20
/**
 * Implementation of the stored procedure SET_ISOLATION_JDBC.
 * Sets the the isolation level given as argument on the default connection
 * using Connection.setTransactionIasolation.
 * @param isolation JDBC isolation level constant representing the 
 * new isolation level
 * @throws java.sql.SQLException
 */
public static void setIsolationJDBC(int isolation)
        throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:default:connection");
    c.setTransactionIsolation(isolation);
}