List of usage examples for java.sql Connection rollback
void rollback(Savepoint savepoint) throws SQLException;
Savepoint object was set. From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
/** * Updates the definition of a view within the DB. This method will not work * if the number of columns within the view are being changed. *///from w w w . j ava 2 s . com private boolean updateViewDbActionWithCreateOrReplace(Connection conn, BaseReportInfo report, boolean viewExists) throws SQLException, CantDoThatException, CodingErrorException, ObjectNotFoundException { String SQLCode = "CREATE OR REPLACE VIEW " + report.getInternalReportName() + " AS (" + report.getSQLForDetail() + ")"; boolean createOrReplaceWorked = true; Savepoint savepoint = null; PreparedStatement statement = null; try { savepoint = conn.setSavepoint("createOrReplaceSavepoint"); statement = conn.prepareStatement(SQLCode); statement.execute(); statement.close(); } catch (SQLException sqlex) { if (viewExists) { createOrReplaceWorked = false; conn.rollback(savepoint); } else { // if view didn't exist already, the error must be more serious // than just the CREATE OR REPLACE not working // logger.error("Requested change to report " + // report.getReportName() // + " would break view. Error = " + sqlex); // logger.error("SQL = " + report.getSQLForDetail()); throw new SQLException("The requested change would cause an error in the report: " + sqlex.getMessage() + ". SQL = " + statement, sqlex.getSQLState(), sqlex); } } return createOrReplaceWorked; }
From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
/** * Updates the definition of a view within the DB. This method should only * be used if updateViewDbActionWithCreateOrReplace fails. Drops the view * and recreates it.// www .j a v a 2 s . c o m */ private boolean updateViewDbActionWithDropAndCreate(Connection conn, BaseReportInfo report) throws SQLException, CantDoThatException, CodingErrorException, ObjectNotFoundException { String CreateViewSQL = "CREATE VIEW " + report.getInternalReportName() + " AS (" + report.getSQLForDetail() + ")"; boolean dropAndCreateWorked = true; Savepoint savepoint = null; try { savepoint = conn.setSavepoint("dropAndCreateSavepoint"); PreparedStatement dropViewStatement = conn .prepareStatement("DROP VIEW " + report.getInternalReportName()); dropViewStatement.execute(); dropViewStatement.close(); PreparedStatement statement = conn.prepareStatement(CreateViewSQL); statement.execute(); statement.close(); } catch (SQLException sqlex) { conn.rollback(savepoint); dropAndCreateWorked = false; } return dropAndCreateWorked; }
From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
public void addCalculationToReport(HttpServletRequest request, Connection conn, SimpleReportInfo report, ReportCalcFieldInfo calculationField) throws SQLException, DisallowedException, InconsistentStateException, CantDoThatException, CodingErrorException, ObjectNotFoundException { if (!(this.authManager.getAuthenticator().loggedInUserAllowedTo(request, PrivilegeType.MANAGE_TABLE, report.getParentTable()))) { throw new DisallowedException(this.authManager.getLoggedInUser(request), PrivilegeType.MANAGE_TABLE, report.getParentTable()); }/*from w ww . jav a2s .c o m*/ HibernateUtil.activateObject(report); Savepoint savepoint = null; report.addCalculation(calculationField); savepoint = conn.setSavepoint("addCalculationSavepoint"); try { this.updateViewDbAction(conn, report, request); } catch (SQLException sqlex) { // detect aggregate functions if (sqlex.getMessage().contains("must appear in the GROUP BY clause") || sqlex.getMessage().contains("aggregates not allowed in GROUP BY clause")) { conn.rollback(savepoint); calculationField.setAggregateFunction(true); this.updateViewDbAction(conn, report, request); } else { throw sqlex; } } UsageLogger usageLogger = new UsageLogger(this.relationalDataSource); AppUserInfo user = this.authManager.getUserByUserName(request, request.getRemoteUser()); usageLogger.logReportSchemaChange(user, report, AppAction.ADD_CALCULATION_TO_REPORT, "calculation name: " + calculationField.getFieldName()); UsageLogger.startLoggingThread(usageLogger); }
From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
public void updateCalculationInReport(HttpServletRequest request, Connection conn, SimpleReportInfo report, ReportCalcFieldInfo calculationField, String calculationName, String calculationDefn, DatabaseFieldType dbFieldType, boolean isReportHidden) throws DisallowedException, SQLException, ObjectNotFoundException, CantDoThatException, CodingErrorException { if (!(this.authManager.getAuthenticator().loggedInUserAllowedTo(request, PrivilegeType.MANAGE_TABLE, report.getParentTable()))) { throw new DisallowedException(this.authManager.getLoggedInUser(request), PrivilegeType.MANAGE_TABLE, report.getParentTable()); }/*w w w. j a v a 2 s . c om*/ HibernateUtil.activateObject(report); Savepoint savepoint = null; boolean definitionUpdate = false; if (!(calculationDefn.toLowerCase().equals(calculationField.getCalculationDefinition())) || !(dbFieldType.equals(calculationField.getDbType()))) { Map<TableInfo, Set<BaseReportInfo>> availableDataStores = this.getViewableDataStores(request); ((ReportCalcFieldDefn) calculationField).updateCalculationDefinition(calculationDefn, dbFieldType, availableDataStores); definitionUpdate = true; } else { ((ReportCalcFieldDefn) calculationField).setBaseFieldName(calculationName); calculationField.setReportHidden(isReportHidden); } if (definitionUpdate) { savepoint = conn.setSavepoint("updateCalculationSavepoint"); try { this.updateViewDbAction(conn, report, request); } catch (SQLException sqlex) { // detect aggregate functions if (sqlex.getMessage().contains("must appear in the GROUP BY clause") || sqlex.getMessage().contains("aggregates not allowed in GROUP BY clause")) { conn.rollback(savepoint); calculationField.setAggregateFunction(true); this.updateViewDbAction(conn, report, request); } else { throw sqlex; } } } UsageLogger usageLogger = new UsageLogger(this.relationalDataSource); AppUserInfo user = this.authManager.getUserByUserName(request, request.getRemoteUser()); usageLogger.logReportSchemaChange(user, report, AppAction.UPDATE_CALCULATION_IN_REPORT, "calculation name: " + calculationField.getFieldName()); UsageLogger.startLoggingThread(usageLogger); }
From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java
/** * Updates the definition of a view within the DB. This method should only * be used if updateViewDbActionWithDropAndCreate and * updateViewDbActionWithCreateOrReplace fails. Drops any dependent views so * that 'report' can be updated. Once report has been updated, all dependent * views are recreated.//from w w w.j a v a2s.c om */ private void updateViewDbActionWithDropAndCreateDependencies(Connection conn, BaseReportInfo report, HttpServletRequest request) throws SQLException, ObjectNotFoundException, CodingErrorException, CantDoThatException { Savepoint savepoint = null; PreparedStatement statement = null; try { savepoint = conn.setSavepoint("dropAndCreateDependenciesSavepoint"); Map<String, List<String>> reportDependencyMap = new HashMap<String, List<String>>(); this.fillViewDependencyMap(conn, report.getInternalReportName(), reportDependencyMap, true); // Remove reports... List<String> deletedReports = new ArrayList<String>(); while (deletedReports.size() < reportDependencyMap.size()) { for (String reportInternalName : reportDependencyMap.keySet()) { if (!deletedReports.contains(reportInternalName)) { boolean cannotDelete = false; for (String dependentReportInternalName : reportDependencyMap.get(reportInternalName)) { if (!deletedReports.contains(dependentReportInternalName)) { cannotDelete = true; } } if (!cannotDelete) { PreparedStatement dropViewStatement = conn .prepareStatement("DROP VIEW " + reportInternalName); dropViewStatement.execute(); dropViewStatement.close(); deletedReports.add(reportInternalName); } } } } // Recreate reports... Collections.reverse(deletedReports); for (String reportInternalName : deletedReports) { TableInfo table = this.findTableContainingReportWithoutChecks(reportInternalName, request); HibernateUtil.activateObject(table); BaseReportInfo reportToRecreate = table.getReport(reportInternalName); String CreateViewSQL = "CREATE VIEW " + reportInternalName + " AS (" + reportToRecreate.getSQLForDetail() + ")"; statement = conn.prepareStatement(CreateViewSQL); statement.execute(); statement.close(); } } catch (SQLException sqlex) { conn.rollback(savepoint); throw new SQLException("The requested change would cause an error in the report: " + sqlex.getMessage() + ". SQL = " + statement, sqlex.getSQLState(), sqlex); } }
From source file:org.apache.hadoop.hive.metastore.txn.TxnHandler.java
/** * Concurrency/isolation notes://from w ww . j ava2 s. c o m * This is mutexed with {@link #openTxns(OpenTxnRequest)} and other {@link #commitTxn(CommitTxnRequest)} * operations using select4update on NEXT_TXN_ID. Also, mutexes on TXNX table for specific txnid:X * see more notes below. * In order to prevent lost updates, we need to determine if any 2 transactions overlap. Each txn * is viewed as an interval [M,N]. M is the txnid and N is taken from the same NEXT_TXN_ID sequence * so that we can compare commit time of txn T with start time of txn S. This sequence can be thought of * as a logical time counter. If S.commitTime < T.startTime, T and S do NOT overlap. * * Motivating example: * Suppose we have multi-statment transactions T and S both of which are attempting x = x + 1 * In order to prevent lost update problem, the the non-overlapping txns must lock in the snapshot * that they read appropriately. In particular, if txns do not overlap, then one follows the other * (assumig they write the same entity), and thus the 2nd must see changes of the 1st. We ensure * this by locking in snapshot after * {@link #openTxns(OpenTxnRequest)} call is made (see {@link org.apache.hadoop.hive.ql.Driver#acquireLocksAndOpenTxn()}) * and mutexing openTxn() with commit(). In other words, once a S.commit() starts we must ensure * that txn T which will be considered a later txn, locks in a snapshot that includes the result * of S's commit (assuming no other txns). * As a counter example, suppose we have S[3,3] and T[4,4] (commitId=txnid means no other transactions * were running in parallel). If T and S both locked in the same snapshot (for example commit of * txnid:2, which is possible if commitTxn() and openTxnx() is not mutexed) * 'x' would be updated to the same value by both, i.e. lost update. */ @Override @RetrySemantics.Idempotent("No-op if already committed") public void commitTxn(CommitTxnRequest rqst) throws NoSuchTxnException, TxnAbortedException, MetaException { long txnid = rqst.getTxnid(); try { Connection dbConn = null; Statement stmt = null; ResultSet lockHandle = null; ResultSet commitIdRs = null, rs; try { lockInternal(); dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED); stmt = dbConn.createStatement(); /** * Runs at READ_COMMITTED with S4U on TXNS row for "txnid". S4U ensures that no other * operation can change this txn (such acquiring locks). While lock() and commitTxn() * should not normally run concurrently (for same txn) but could due to bugs in the client * which could then corrupt internal transaction manager state. Also competes with abortTxn(). */ lockHandle = lockTransactionRecord(stmt, txnid, TXN_OPEN); if (lockHandle == null) { //if here, txn was not found (in expected state) TxnStatus actualTxnStatus = findTxnState(txnid, stmt); if (actualTxnStatus == TxnStatus.COMMITTED) { /** * This makes the operation idempotent * (assume that this is most likely due to retry logic) */ LOG.info("Nth commitTxn(" + JavaUtils.txnIdToString(txnid) + ") msg"); return; } raiseTxnUnexpectedState(actualTxnStatus, txnid); shouldNeverHappen(txnid); //dbConn is rolled back in finally{} } String conflictSQLSuffix = "from TXN_COMPONENTS where tc_txnid=" + txnid + " and tc_operation_type IN(" + quoteChar(OpertaionType.UPDATE.sqlConst) + "," + quoteChar(OpertaionType.DELETE.sqlConst) + ")"; rs = stmt.executeQuery(sqlGenerator.addLimitClause(1, "tc_operation_type " + conflictSQLSuffix)); if (rs.next()) { close(rs); //if here it means currently committing txn performed update/delete and we should check WW conflict /** * This S4U will mutex with other commitTxn() and openTxns(). * -1 below makes txn intervals look like [3,3] [4,4] if all txns are serial * Note: it's possible to have several txns have the same commit id. Suppose 3 txns start * at the same time and no new txns start until all 3 commit. * We could've incremented the sequence for commitId is well but it doesn't add anything functionally. */ commitIdRs = stmt .executeQuery(sqlGenerator.addForUpdateClause("select ntxn_next - 1 from NEXT_TXN_ID")); if (!commitIdRs.next()) { throw new IllegalStateException("No rows found in NEXT_TXN_ID"); } long commitId = commitIdRs.getLong(1); Savepoint undoWriteSetForCurrentTxn = dbConn.setSavepoint(); /** * "select distinct" is used below because * 1. once we get to multi-statement txns, we only care to record that something was updated once * 2. if {@link #addDynamicPartitions(AddDynamicPartitions)} is retried by caller it my create * duplicate entries in TXN_COMPONENTS * but we want to add a PK on WRITE_SET which won't have unique rows w/o this distinct * even if it includes all of it's columns */ int numCompsWritten = stmt.executeUpdate( "insert into WRITE_SET (ws_database, ws_table, ws_partition, ws_txnid, ws_commit_id, ws_operation_type)" + " select distinct tc_database, tc_table, tc_partition, tc_txnid, " + commitId + ", tc_operation_type " + conflictSQLSuffix); /** * see if there are any overlapping txns wrote the same element, i.e. have a conflict * Since entire commit operation is mutexed wrt other start/commit ops, * committed.ws_commit_id <= current.ws_commit_id for all txns * thus if committed.ws_commit_id < current.ws_txnid, transactions do NOT overlap * For example, [17,20] is committed, [6,80] is being committed right now - these overlap * [17,20] committed and [21,21] committing now - these do not overlap. * [17,18] committed and [18,19] committing now - these overlap (here 18 started while 17 was still running) */ rs = stmt.executeQuery(sqlGenerator.addLimitClause(1, "committed.ws_txnid, committed.ws_commit_id, committed.ws_database," + "committed.ws_table, committed.ws_partition, cur.ws_commit_id cur_ws_commit_id, " + "cur.ws_operation_type cur_op, committed.ws_operation_type committed_op " + "from WRITE_SET committed INNER JOIN WRITE_SET cur " + "ON committed.ws_database=cur.ws_database and committed.ws_table=cur.ws_table " + //For partitioned table we always track writes at partition level (never at table) //and for non partitioned - always at table level, thus the same table should never //have entries with partition key and w/o "and (committed.ws_partition=cur.ws_partition or (committed.ws_partition is null and cur.ws_partition is null)) " + "where cur.ws_txnid <= committed.ws_commit_id" + //txns overlap; could replace ws_txnid // with txnid, though any decent DB should infer this " and cur.ws_txnid=" + txnid + //make sure RHS of join only has rows we just inserted as // part of this commitTxn() op " and committed.ws_txnid <> " + txnid + //and LHS only has committed txns //U+U and U+D is a conflict but D+D is not and we don't currently track I in WRITE_SET at all " and (committed.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + " OR cur.ws_operation_type=" + quoteChar(OpertaionType.UPDATE.sqlConst) + ")")); if (rs.next()) { //found a conflict String committedTxn = "[" + JavaUtils.txnIdToString(rs.getLong(1)) + "," + rs.getLong(2) + "]"; StringBuilder resource = new StringBuilder(rs.getString(3)).append("/") .append(rs.getString(4)); String partitionName = rs.getString(5); if (partitionName != null) { resource.append('/').append(partitionName); } String msg = "Aborting [" + JavaUtils.txnIdToString(txnid) + "," + rs.getLong(6) + "]" + " due to a write conflict on " + resource + " committed by " + committedTxn + " " + rs.getString(7) + "/" + rs.getString(8); close(rs); //remove WRITE_SET info for current txn since it's about to abort dbConn.rollback(undoWriteSetForCurrentTxn); LOG.info(msg); //todo: should make abortTxns() write something into TXNS.TXN_META_INFO about this if (abortTxns(dbConn, Collections.singletonList(txnid), true) != 1) { throw new IllegalStateException(msg + " FAILED!"); } dbConn.commit(); close(null, stmt, dbConn); throw new TxnAbortedException(msg); } else { //no conflicting operations, proceed with the rest of commit sequence } } else { /** * current txn didn't update/delete anything (may have inserted), so just proceed with commit * * We only care about commit id for write txns, so for RO (when supported) txns we don't * have to mutex on NEXT_TXN_ID. * Consider: if RO txn is after a W txn, then RO's openTxns() will be mutexed with W's * commitTxn() because both do S4U on NEXT_TXN_ID and thus RO will see result of W txn. * If RO < W, then there is no reads-from relationship. */ } // Move the record from txn_components into completed_txn_components so that the compactor // knows where to look to compact. String s = "insert into COMPLETED_TXN_COMPONENTS select tc_txnid, tc_database, tc_table, " + "tc_partition from TXN_COMPONENTS where tc_txnid = " + txnid; LOG.debug("Going to execute insert <" + s + ">"); int modCount = 0; if ((modCount = stmt.executeUpdate(s)) < 1) { //this can be reasonable for an empty txn START/COMMIT or read-only txn //also an IUD with DP that didn't match any rows. LOG.info("Expected to move at least one record from txn_components to " + "completed_txn_components when committing txn! " + JavaUtils.txnIdToString(txnid)); } s = "delete from TXN_COMPONENTS where tc_txnid = " + txnid; LOG.debug("Going to execute update <" + s + ">"); modCount = stmt.executeUpdate(s); s = "delete from HIVE_LOCKS where hl_txnid = " + txnid; LOG.debug("Going to execute update <" + s + ">"); modCount = stmt.executeUpdate(s); s = "delete from TXNS where txn_id = " + txnid; LOG.debug("Going to execute update <" + s + ">"); modCount = stmt.executeUpdate(s); LOG.debug("Going to commit"); dbConn.commit(); } catch (SQLException e) { LOG.debug("Going to rollback"); rollbackDBConn(dbConn); checkRetryable(dbConn, e, "commitTxn(" + rqst + ")"); throw new MetaException( "Unable to update transaction database " + StringUtils.stringifyException(e)); } finally { close(commitIdRs); close(lockHandle, stmt, dbConn); unlockInternal(); } } catch (RetryException e) { commitTxn(rqst); } }