Using Transactions使用事务

There are times when you do not want one statement to take effect unless another one completes. 有时，您不希望一条语句生效，除非另一条语句完成。For example, when the proprietor of The Coffee Break updates the amount of coffee sold each week, the proprietor will also want to update the total amount sold to date. 例如，当咖啡休息时间的所有者更新每周售出的咖啡量时，所有者还将希望更新到目前为止售出的咖啡总量。However, the amount sold per week and the total amount sold should be updated at the same time; otherwise, the data will be inconsistent. 但是，每周的销售额和总销售额应同时更新；否则，数据将不一致。The way to be sure that either both actions occur or neither action occurs is to use a transaction. 确保两个操作都发生或两个操作都不发生的方法是使用事务。A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed.事务是一组作为一个单元执行的一个或多个语句，因此要么执行所有语句，要么不执行任何语句。

This page covers the following topics本页涵盖以下主题

• Disabling Auto-Commit Mode禁用自动提交模式
• Committing Transactions提交事务
• Using Transactions to Preserve Data Integrity使用事务保持数据完整性
• Setting and Rolling Back to Savepoints设置并回滚到保存点
• Releasing Savepoints释放保存点
• When to Call Method rollback何时调用方法rollback

Disabling Auto-Commit Mode禁用自动提交模式

When a connection is created, it is in auto-commit mode. 创建连接时，它处于自动提交模式。This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. 这意味着每个单独的SQL语句都被视为一个事务，并在执行后立即自动提交。(To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. （更准确地说，默认情况是SQL语句在完成时提交，而不是在执行时提交。A statement is completed when all of its result sets and update counts have been retrieved. 当检索到一条语句的所有结果集和更新计数时，该语句即告完成。In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)但是，在几乎所有情况下，语句都是在执行后立即完成并提交的。）

The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. 允许将两个或多个语句分组到一个事务中的方法是禁用自动提交模式。This is demonstrated in the following code, where con is an active connection:以下代码对此进行了说明，其中con为活动连接：

con.setAutoCommit(false);

Committing Transactions提交事务

After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. 禁用自动提交模式后，在显式调用commit方法之前，不会提交任何SQL语句。All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit. 上次调用commit方法后执行的所有语句都包含在当前事务中，并作为一个单元一起提交。The following method, CoffeesTable.updateCoffeeSales, in which con is an active connection, illustrates a transaction:以下方法CoffeesTable.updateCoffeeSales说明了一个事务，其中con是活动连接：

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {
    String updateString =
      "update COFFEES set SALES = ? where COF_NAME = ?";
    String updateStatement =
      "update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?";

    try (PreparedStatement updateSales = con.prepareStatement(updateString);
         PreparedStatement updateTotal = con.prepareStatement(updateStatement))
    
    {
      con.setAutoCommit(false);
      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    }
  }

In this method, the auto-commit mode is disabled for the connection con, which means that the two prepared statements updateSales and updateTotal are committed together when the method commit is called. 在此方法中，连接con禁用了自动提交模式，这意味着调用方法commit时，两个准备好的语句updateSales和updateTotal一起提交。Whenever the commit method is called (either automatically when auto-commit mode is enabled or explicitly when it is disabled), all changes resulting from statements in the transaction are made permanent. 每当调用commit方法时（无论是在启用自动提交模式时自动调用，还是在禁用该方法时显式调用），事务中的语句所导致的所有更改都是永久性的。In this case, that means that the SALES and TOTAL columns for Colombian coffee have been changed to 50 (if TOTAL had been 0 previously) and will retain this value until they are changed with another update statement.在本例中，这意味着哥伦比亚咖啡的SALES和TOTAL列已更改为50（如果之前TOTAL为0），并将保留此值，直到使用另一个更新语句更改为止。

The statement con.setAutoCommit(true); enables auto-commit mode, which means that each statement is once again committed automatically when it is completed. 语句con.setAutoCommit(true);启用自动提交模式，这意味着每个语句在完成时将再次自动提交。Then, you are back to the default state where you do not have to call the method commit yourself. 然后，您回到默认状态，在那里您不必自己调用commit方法。It is advisable to disable the auto-commit mode only during the transaction mode. 建议仅在事务模式期间禁用自动提交模式。This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.通过这种方式，您可以避免为多个语句持有数据库锁，这会增加与其他用户发生冲突的可能性。

Using Transactions to Preserve Data Integrity使用事务保持数据完整性

In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. 除了将语句分组作为一个单元执行外，事务还有助于保持表中数据的完整性。For instance, imagine that an employee was supposed to enter new coffee prices in the table COFFEES but delayed doing it for a few days. 例如，假设一名员工本应在COFFEES表上输入新的咖啡价格，但却延迟了几天。In the meantime, prices rose, and today the owner is in the process of entering the higher prices. 与此同时，价格上涨，今天业主正在进入更高的价格。The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. 在所有者试图更新表的同时，员工终于开始输入现在已经过时的价格。After inserting the outdated prices, the employee realizes that they are no longer valid and calls the Connection method rollback to undo their effects. 插入过期价格后，员工意识到这些价格不再有效，并调用Connection方法rollback撤消其效果。(The method rollback aborts a transaction and restores values to what they were before the attempted update.) （rollback方法中止事务并将值恢复到尝试更新之前的值。）At the same time, the owner is executing a SELECT statement and printing the new prices. 同时，所有者正在执行SELECT语句并打印新的价格。In this situation, it is possible that the owner will print a price that had been rolled back to its previous value, making the printed price incorrect.在这种情况下，所有者可能会打印已回滚到其先前值的价格，从而使打印的价格不正确。

This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time.通过使用事务可以避免这种情况，为两个用户同时访问数据时产生的冲突提供一定程度的保护。

To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. 为了避免事务期间的冲突，DBMS使用锁，这是一种阻止其他人访问事务正在访问的数据的机制。(Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) （请注意，在自动提交模式中，每个语句都是事务，锁只为一个语句持有。）After a lock is set, it remains in force until the transaction is committed or rolled back. 设置锁后，它将保持有效，直到提交或回滚事务。For example, a DBMS could lock a row of a table until updates to it have been committed. 例如，DBMS可以锁定表中的一行，直到提交更新为止。The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. 这个锁的作用是防止用户获得脏读，也就是说，在值被永久化之前读取它。(Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. （访问尚未提交的更新值被视为脏读，因为该值可能回滚到其以前的值。If you read a value that is later rolled back, you will have read an invalid value.)如果读取了稍后回滚的值，则将读取无效值。）

How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.锁的设置方式由所谓的事务隔离级别决定，该级别可以是完全不支持事务，也可以是支持执行非常严格的访问规则的事务。

One example of a transaction isolation level is TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed. 事务隔离级别的一个示例是TRANSACTION_READ_COMMITTED，它在提交值之前不允许访问该值。In other words, if the transaction isolation level is set to TRANSACTION_READ_COMMITTED, the DBMS does not allow dirty reads to occur. 换句话说，如果事务隔离级别设置为TRANSACTION_READ_COMMITTED，则DBMS不允许脏读发生。The interface Connection includes five values that represent the transaction isolation levels you can use in JDBC:接口Connection包括五个值，它们表示可以在JDBC中使用的事务隔离级别：

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. 当事务A检索行，事务B随后更新行，事务A稍后再次检索同一行时，将发生不可重复读取。Transaction A retrieves the same row twice but sees different data.事务A检索同一行两次，但看到不同的数据。

A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. 当事务A检索满足给定条件的一组行，事务B随后插入或更新一行，使该行现在满足事务A中的条件，事务A随后重复条件检索时，会发生幻像读取。Transaction A now sees an additional row. 事务A现在会看到另一行。This row is referred to as a phantom.这一行被称为幻影。

Usually, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. 通常，您不需要对事务隔离级别做任何事情；您可以只为您的DBMS使用默认值。The default transaction isolation level depends on your DBMS. 默认事务隔离级别取决于您的DBMS。For example, for Java DB, it is TRANSACTION_READ_COMMITTED. 例如，对于Java DB，它是TRANSACTION_READ_COMMITTED。JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation).JDBC允许您找出DBMS设置为哪个事务隔离级别（使用Connection方法getTransactionIsolation），还允许您将其设置为另一个级别（使用Connection方法setTransactionIsolation）。

Note: A JDBC driver might not support all transaction isolation levels. 注意：JDBC驱动程序可能不支持所有事务隔离级别。If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, the driver can substitute a higher, more restrictive transaction isolation level. 如果驱动程序不支持在调用setTransactionIsolation时指定的隔离级别，则该驱动程序可以替换更高、更严格的事务隔离级别。If a driver cannot substitute a higher transaction level, it throws a SQLException. 如果驱动程序无法替换更高的事务级别，则会抛出SQLException。Use the method DatabaseMetaData.supportsTransactionIsolationLevel to determine whether or not the driver supports a given level.使用DatabaseMetaData.supportsTransactionIsolationLevel方法确定驱动程序是否支持给定级别。

Setting and Rolling Back to Savepoints设置并回滚到保存点

The method Connection.setSavepoint, sets a Savepoint object within the current transaction. 方法Connection.setSavepoint在当前事务中设置保存点对象。The Connection.rollback method is overloaded to take a Savepoint argument.重载Connection.rollback方法以获取保存点参数。

The following method, CoffeesTable.modifyPricesByPercentage, raises the price of a particular coffee by a percentage, priceModifier. 以下方法CoffeesTable.modifyPricesByPercentage将特定咖啡的价格提高一个百分比priceModifier。However, if the new price is greater than a specified price, maximumPrice, then the price is reverted to the original price:但是，如果新价格大于指定价格maximumPrice，则该价格将恢复为原始价格：

public void modifyPricesByPercentage(
    String coffeeName,
    float priceModifier,
    float maximumPrice) throws SQLException {
    con.setAutoCommit(false);
    ResultSet rs = null;
    String priceQuery = "SELECT COF_NAME, PRICE FROM COFFEES " +
                        "WHERE COF_NAME = ?";
    String updateQuery = "UPDATE COFFEES SET PRICE = ? " +
                         "WHERE COF_NAME = ?";
    try (PreparedStatement getPrice = con.prepareStatement(priceQuery, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
         PreparedStatement updatePrice = con.prepareStatement(updateQuery))
    {
      Savepoint save1 = con.setSavepoint();
      getPrice.setString(1, coffeeName);
      if (!getPrice.execute()) {
        System.out.println("Could not find entry for coffee named " + coffeeName);
      } else {
        rs = getPrice.getResultSet();
        rs.first();
        float oldPrice = rs.getFloat("PRICE");
        float newPrice = oldPrice + (oldPrice * priceModifier);
        System.out.printf("Old price of %s is $%.2f%n", coffeeName, oldPrice);
        System.out.printf("New price of %s is $%.2f%n", coffeeName, newPrice);
        System.out.println("Performing update...");
        updatePrice.setFloat(1, newPrice);
        updatePrice.setString(2, coffeeName);
        updatePrice.executeUpdate();
        System.out.println("\nCOFFEES table after update:");
        CoffeesTable.viewTable(con);
        if (newPrice > maximumPrice) {
          System.out.printf("The new price, $%.2f, is greater " +
                            "than the maximum price, $%.2f. " +
                            "Rolling back the transaction...%n",
                            newPrice, maximumPrice);
          con.rollback(save1);
          System.out.println("\nCOFFEES table after rollback:");
          CoffeesTable.viewTable(con);
        }
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      con.setAutoCommit(true);
    }
  }

The following statement specifies that the cursor of the ResultSet object generated from the getPrice query is closed when the commit method is called. 下面的语句指定在调用commit方法时关闭从getPrice查询生成的ResultSet对象的游标。Note that if your DBMs does not support ResultSet.CLOSE_CURSORS_AT_COMMIT, then this constant is ignored:请注意，如果DBMs不支持ResultSet.CLOSE_CURSORS_AT_COMMIT，则忽略此常量：

getPrice = con.prepareStatement(query, ResultSet.CLOSE_CURSORS_AT_COMMIT);

The method begins by creating a Savepoint with the following statement:该方法首先使用以下语句创建Savepoint：

Savepoint save1 = con.setSavepoint();

The method checks if the new price is greater than the maximumPrice value. 该方法检查新价格是否大于maximumPrice值。If so, the method rolls back the transaction with the following statement:如果是，该方法将使用以下语句回滚事务：

con.rollback(save1);

Consequently, when the method commits the transaction by calling the Connection.commit method, it will not commit any rows whose associated Savepoint has been rolled back; it will commit all the other updated rows.因此，当该方法通过调用Connection.commit方法提交事务时，它不会提交其关联的Savepoint已回滚的任何行；它将提交所有其他更新的行。

Releasing Savepoints释放保存点

The method Connection.releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction.方法Connection.releaseSavepoint将Savepoint对象作为参数，并将其从当前事务中删除。

After a savepoint has been released, attempting to reference it in a rollback operation causes a SQLException to be thrown. 释放保存点后，尝试在回滚操作中引用它会导致引发SQLException。Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. 在事务中创建的任何保存点都会自动释放，并在提交事务或回滚整个事务时失效。Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.将事务回滚到保存点会自动释放并使在相关保存点之后创建的任何其他保存点无效。

When to Call Method rollback何时调用方法rollback

As mentioned earlier, calling the method rollback terminates a transaction and returns any values that were modified to their previous values. 如前所述，调用rollback方法终止事务并返回修改为以前值的所有值。If you are trying to execute one or more statements in a transaction and get a SQLException, call the method rollback to end the transaction and start the transaction all over again. 如果试图在事务中执行一个或多个语句并获取SQLException，请调用rollback方法结束事务并重新启动事务。That is the only way to know what has been committed and what has not been committed. 这是了解已经犯下什么和没有犯下什么的唯一途径。Catching a SQLException tells you that something is wrong, but it does not tell you what was or was not committed. 捕获SQLException会告诉您发生了错误，但它不会告诉您发生了什么或没有发生什么。Because you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be certain.因为您不能指望什么都没有提交，所以调用rollback方法是唯一确定的方法。

The method CoffeesTable.updateCoffeeSales demonstrates a transaction and includes a catch block that invokes the method rollback. 方法CoffeesTable.updateCoffeeSales演示了一个事务，并包含一个调用方法rollback的catch块。If the application continues and uses the results of the transaction, this call to the rollback method in the catch block prevents the use of possibly incorrect data.如果应用程序继续并使用事务的结果，那么对catch块中的rollback方法的调用将防止使用可能不正确的数据。