处理暂时性错误并有效地连接到 Azure Database for MySQLHandle transient errors and connect efficiently to Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本文介绍如何处理暂时性错误并有效地连接到 Azure Database for MySQL。This article describes how to handle transient errors and connect efficiently to Azure Database for MySQL.

暂时性错误Transient errors

暂时性错误也称为暂时性故障,是一种可以自行解决的错误。A transient error, also known as a transient fault, is an error that will resolve itself. 这些错误往往表现为与数据库服务器的连接断开。Most typically these errors manifest as a connection to the database server being dropped. 此外,无法与服务器建立的新连接。Also new connections to a server can't be opened. 例如,在发生硬件或网络故障时,可能会出现暂时性错误。Transient errors can occur for example when hardware or network failure happens. 另一个可能的原因是正在推出 PaaS 服务的新版本。系统在 60 秒以内可自动解决其中的大部分事件。Another reason could be a new version of a PaaS service that is being rolled out. Most of these events are automatically mitigated by the system in less than 60 seconds. 设计和开发云中的应用程序时,预料到会出现暂时性错误是最佳做法。A best practice for designing and developing applications in the cloud is to expect transient errors. 假设这些错误随时可能在任意组件中发生,并部署相应的逻辑来应对这种情况。Assume they can happen in any component at any time and to have the appropriate logic in place to handle these situations.

处理暂时性错误Handling transient errors

应使用重试逻辑来处理暂时性错误。Transient errors should be handled using retry logic. 必须考虑的情况包括:Situations that must be considered:

  • 尝试打开连接时出错An error occurs when you try to open a connection
  • 服务器端的空闲连接断开。An idle connection is dropped on the server side. 尝试发出的命令无法执行When you try to issue a command it can't be executed
  • 当前正在用于执行命令的活动连接断开。An active connection that currently is executing a command is dropped.

第一种和第二种情况的处理方式非常直接。The first and second case are fairly straight forward to handle. 可以再试打开连接。Try to open the connection again. 如果连接成功,则表示系统解决了暂时性错误。When you succeed, the transient error has been mitigated by the system. 可以再次使用 Azure Database for MySQL。You can use your Azure Database for MySQL again. 我们建议在重试连接之前等待一段时间。We recommend having waits before retrying the connection. 如果初始重试失败,则回退。Back off if the initial retries fail. 这样,系统便可以使用所有可用资源来解决错误局面。This way the system can use all resources available to overcome the error situation. 遵循的良好模式是:A good pattern to follow is:

  • 在首次重试之前等待 5 秒。Wait for 5 seconds before your first retry.
  • 对于每次后续重试,以指数级增大等待时间,最长为 60 秒。For each following retry, the increase the wait exponentially, up to 60 seconds.
  • 设置最大重试次数,达到该次数时,应用程序认为操作失败。Set a max number of retries at which point your application considers the operation failed.

活动事务的连接失败时,适当地处理恢复会更困难。When a connection with an active transaction fails, it is more difficult to handle the recovery correctly. 存在两种情况:如果事务在性质上是只读的,则可以安全地重新打开连接并重试事务。There are two cases: If the transaction was read-only in nature, it is safe to reopen the connection and to retry the transaction. 但是,如果事务也在写入数据库,则必须确定事务在发生暂时性错误之前是已回滚还是已成功。If however the transaction was also writing to the database, you must determine if the transaction was rolled back, or if it succeeded before the transient error happened. 在这种情况下,你可能尚未从数据库服务器收到提交确认。In that case, you might just not have received the commit acknowledgment from the database server.

解决此问题的方法之一是,在客户端上生成一个用于所有重试的唯一 ID。One way of doing this, is to generate a unique ID on the client that is used for all the retries. 将此唯一 ID 作为事务的一部分传递给服务器,并将其存储在具有唯一约束的列中。You pass this unique ID as part of the transaction to the server and to store it in a column with a unique constraint. 这样,便可以安全重试事务。This way you can safely retry the transaction. 如果前一事务已回滚,并且客户端生成的唯一 ID 在系统中尚不存在,则重试将会成功。It will succeed if the previous transaction was rolled back and the client-generated unique ID does not yet exist in the system. 如果之前已存储该唯一 ID(因为前一事务已成功完成),则重试将会失败,并指示重复键冲突。It will fail indicating a duplicate key violation if the unique ID was previously stored because the previous transaction completed successfully.

如果程序通过第三方中间件来与 Azure Database for MySQL 通信,请咨询供应商该中间件是否包含暂时性错误的重试逻辑。When your program communicates with Azure Database for MySQL through third-party middleware, ask the vendor whether the middleware contains retry logic for transient errors.

请务必测试重试逻辑。Make sure to test you retry logic. 例如,尝试在纵向扩展或缩减 Azure Database for MySQL 服务器的计算资源时执行代码。For example, try to execute your code while scaling up or down the compute resources of your Azure Database for MySQL server. 应用程序应可处理此操作期间遇到的短暂停机,而不会出现任何问题。Your application should handle the brief downtime that is encountered during this operation without any problems.

有效连接到 Azure Database for MySQLConnect efficiently to Azure Database for MySQL

数据库连接是有限的资源,因此,有效利用连接池访问 Azure Database for MySQL 可以优化性能。Database connections are a limited resource, so making effective use of connection pooling to access Azure Database for MySQL optimizes performance. 以下部分介绍如何使用连接池或持久性连接来更有效地访问 Azure Database for MySQL。The below section explains how to use connection pooling or persistent connections to more effectively access Azure Database for MySQL.

管理数据库连接可能会对整个应用程序的性能造成很大的影响。Managing database connections can have a significant impact on the performance of the application as a whole. 若要优化应用程序的性能,目标应是减少建立连接的次数,以及在关键代码路径中建立连接的时间。To optimize the performance of your application, the goal should be to reduce the number of times connections are established and time for establishing connections in key code paths. 我们强烈建议使用数据库连接池或持久性连接来与 Azure Database for MySQL 建立连接。We strongly recommend using database connection pooling or persistent connections to connect to Azure Database for MySQL. 数据库连接池将处理数据库连接的创建、管理和分配。Database connection pooling handles the creation, management, and allocation of database connections. 当某个程序请求数据库连接时,它会优先分配现有的空闲数据库连接,而不是创建新的连接。When a program requests a database connection, it prioritizes the allocation of existing idle database connections, rather than the creation of a new connection. 该程序用完数据库连接后,该连接将会恢复,以供进一步使用,而不是直接关闭。After the program has finished using the database connection, the connection is recovered in preparation for further use, rather than simply being closed down.

为方便演示,本文提供了一段使用 JAVA 的示例代码For better illustration, this article provides a piece of sample code that uses JAVA as an example. 有关详细信息,请参阅 Apache 通用 DBCPFor more information, see Apache common DBCP.

备注

服务器将配置一个超时机制,以便在某个连接空闲一段时间后将其关闭,从而释放资源。The server configures a timeout mechanism to close a connection that has been in an idle state for some time to free up resources. 请务必设置验证系统,以确保在使用持久性连接时这些连接保持有效性。Be sure to set up the verification system to ensure the effectiveness of persistent connections when you are using them. 有关详细信息,请参阅在客户端中配置验证系统以确保持久性连接的有效性For more information, see Configure verification systems on the client side to ensure the effectiveness of persistent connections.

持久性连接的概念类似于连接池的概念。The concept of persistent connections is similar to that of connection pooling. 将短期连接替换为持久性连接只需对代码进行轻微的更改,但在许多典型的应用方案中,这种做法可以大幅提高性能。Replacing short connections with persistent connections requires only minor changes to the code, but it has a major effect in terms of improving performance in many typical application scenarios.

结合短期连接使用等待和重试机制访问数据库Access databases by using wait and retry mechanism with short connections

如果资源有限制,我们强烈建议使用数据库池或持久性连接来访问数据库。If you have resource limitations, we strongly recommend that you use database pooling or persistent connections to access databases. 如果应用程序使用短期连接,并在即将达到并发连接数的上限时遇到连接故障,则你可以尝试等待和重试机制。If your application use short connections and experience connection failures when you approach the upper limit on the number of concurrent connections,you can try wait and retry mechanism. 可以设置适当的等待时间,在第一次尝试后使用较短的等待时间。You can set an appropriate wait time, with a shorter wait time after the first attempt. 然后,可以尝试等待事件多次。Thereafter, you can try waiting for events multiple times.

在客户端中配置验证机制以确认持久性连接的有效性Configure verification mechanisms in clients to confirm the effectiveness of persistent connections

服务器将配置一个超时机制,以便在某个连接空闲一段时间后将其关闭,从而释放资源。The server configures a timeout mechanism to close a connection that's been in an idle state for some time to free up resources. 当客户端再次访问数据库时,相当于在客户端和服务器之间创建新的连接请求。When the client accesses the database again, it's equivalent to creating a new connection request between the client and the server. 为了确保在使用连接过程中的连接有效性,请在客户端中配置验证机制。To ensure the effectiveness of connections during the process of using them, configure a verification mechanism on the client. 如以下示例中所示,可以使用 Tomcat JDBC 连接池来配置此验证机制。As shown in the following example, you can use Tomcat JDBC connection pooling to configure this verification mechanism.

设置 TestOnBorrow 参数后,当有新的请求时,连接池会自动验证任何可用空闲连接的有效性。By setting the TestOnBorrow parameter, when there's a new request, the connection pool automatically verifies the effectiveness of any available idle connections. 如果此类连接有效,它会直接返回,否则连接池将收回连接。If such a connection is effective, its directly returned otherwise connection pool withdraws the connection. 然后,连接池会创建新的有效连接并将其返回。The connection pool then creates a new effective connection and returns it. 此过程可确保有效访问数据库。This process ensures that database is accessed efficiently.

有关具体设置的信息,请参阅 JDBC 连接池官方简介文档For information on the specific settings, see the JDBC connection pool official introduction document. 主要需要设置以下三个参数:TestOnBorrow(设置为 true)、ValidationQuery(设置为 SELECT 1)和 ValidationQueryTimeout(设置为 1)。You mainly need to set the following three parameters: TestOnBorrow (set to true), ValidationQuery (set to SELECT 1), and ValidationQueryTimeout (set to 1). 具体的示例代码如下所示:The specific sample code is shown below:

public class SimpleTestOnBorrowExample {
      public static void main(String[] args) throws Exception {
          PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
            // The indication of whether objects will be validated by the idle object evictor (if any). 
            // If an object fails to validate, it will be dropped from the pool. 
            // NOTE - for a true value to have any effect, the validationQuery or validatorClassName parameter must be set to a non-null string. 
          p.setTestOnBorrow(true); 

            // The SQL query that will be used to validate connections from this pool before returning them to the caller.
            // If specified, this query does not have to return any data, it just can't throw a SQLException.
          p.setValidationQuery("SELECT 1");

            // The timeout in seconds before a connection validation queries fail. 
            // This works by calling java.sql.Statement.setQueryTimeout(seconds) on the statement that executes the validationQuery. 
            // The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts. 
            // A value less than or equal to zero will disable this feature.
          p.setValidationQueryTimeout(1);
            // set other useful pool properties.
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);

          Connection con = null;
          try {
            con = datasource.getConnection();
            // execute your query here
          } finally {
            if (con!=null) try {con.close();}catch (Exception ignore) {}
          }
      }
  }

后续步骤Next steps