Limax Project

7.16 JDBC Connection pool

The common connection pools such as c3p0 directly assign their own managed connections to the application. Most JDBC methods will throw a SQLException, and it is not realistic to require that every SQL operation handle the connection accident correctly. Typically, after the database is restarted, the initial SQL operation will often fail, and then the connection pool will recover all connections. The issue that Mysql's Connector/J throws a CommunicationsException is not uncommon. So Limax provides a simple connection pool implementation that determines whether to restart the operation by distinguishing SQLException, and avoids propagating issues that can be resolved by the restart operation to the application.

7.16.1 Fundamental

1. The connection is provided to the application through the consumer. The SQL operation set is completed within a specific scope, so that all SQLException may be supervised by the connection pool, and the consumer has a chance to be restarted. Here two scope support are provided, function scope and thread scope (cross-thread transfer connection is not supported, which is not necessary. Even if a regular connection pool is used, the thread requests a new connection from the connection pool, not private exchange).

2. By restarting the consumer, it also solves the exceptions such as the deadlock and timeout.

7.16.2 Programming interface
- Interface limax.sql.SQLConnectionConsumer
  
  Application implements this interface to package SQL operation collection.
- limax.sql.SQLExecutor interface
  
  Define method void execute(SQLConnectionConsumer consumer) throws Exception
  
  Define constants COMMIT and ROLLBACK, which are dedicated to thread-scope connection pool.
  
  execute(SQLExecutor.COMMIT) commit transaction.
  
  execute(SQLExecutor.ROLLBACK) rollback transaction.
- Class limax.sql.SQLPooledExecutor
  
  Constructor:
```
SQLPooledExecutor(String url, int size, boolean threadAffinity, Consumer<Exception> logger)
```
  The url is the database connection url; the size determines the connection pool size; the threadAffinity determines whether the connection pool is a function scope or a thread scope; the logger is used to record the exceptions that cause the consumer to restart, which is convenient for the application to check the restart reason and improve the design.
```
SQLPooledExecutor(String url, int size, Consumer<java.lang.Exception> logger) 
SQLPooledExecutor(String url, int size, boolean threadAffinity) 
SQLPooledExecutor(String url, int size)
```
  The simplified version of the constructor does not contain threadAffinity parameter. The default threadAffinity is false.
  
  When the constructor returns, the size connections have been established, which ensures that the database recognizes the connection url and allows size connections to be established. If the constructor does not return, it should check if the database server is started. In this case, the logger will continue to output the exception that causes reconnection.
  
  Member function:
```
void execute(SQLConnectionConsumer consumer) throws Exception
```
  Execute the SQL operation set provided by the application in the consumer, the consumer does not need to catch the exception, especially the SQLException. The exception should be caught (catch) outside execute, to ensure that the connection pool can check all the exceptions to decide whether to restart. The exception causing the restart is recorded by logger, and the exception without causing the restart is thrown out by execute.
```
void shutdown()
```
  Close the connection pool and reject the new execution request.
- Class limax.sql.RestartTransactionException
  
  The thread-scope connection pool uses this exception to indicate that the transaction needs to be restarted.

7.16.3 Function scope connection pool

An execute can complete the entire transaction, using a function-scope connection pool. This type of connection pool is sufficient in most cases.

Characteristic

1. The connection is initialized to auto-commit mode.

2. After an execute, regardless of success or failure, the connection is reset to auto-commit mode.

3. To execute a multi-statement transaction, auto-commit should first be set to false.

Example

public final class MysqlTest {
    public static void main(String[] args) throws Exception {
        SQLPooledExecutor executor = new SQLPooledExecutor(
            "jdbc:mysql://192.168.1.3:3306/test?user=root&password=admin&characterEncoding=utf8", 
            3, e -> e.printStackTrace());
        Thread.sleep(10000);
        executor.execute(conn -> {
            conn.setAutoCommit(false);
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO test(name) VALUES(?)")) {
                ps.setString(1, "A");
                ps.executeUpdate();
                ps.setString(1, "B");
                ps.executeUpdate();
            }   
        });
        executor.shutdown();
    }
}

During the 10 seconds of sleep, restart the database and observe the output. After the execution, both records are inserted into the database.

7.16.4 Thread scope connection pool

Thread-scope connection pool spans multiple execute operations. Unless a complex application, this type of connection pool is rarely used. The most typical application is the writer connection pool in limax.zdb.LoggerMysql.java, all modifications initiated by a checkpoint operation is assembled as a transaction.
- Characteristic
  
  1. The connection is initialized to non-auto-commit mode, and the consumer is prohibited from setting auto-commit.
  
  2. The thread calls execute for the first time, and the connection is assigned to the thread, which means the beginning of the transaction.
  
  3. Any once an execute throws an exception or calls execute with SQLExecutor.COMMIT or SQLExecutor.ROLLBACK as the consumer, the connection will be reclaimed by the connection pool, and the transaction ends.
  
  4. Any once an execute throws an exception, the transaction rolls back and ends. Among them, RestartTransactionException indicates that the application can restart the transaction.
- Example
```
public final class MysqlTest {
    private static SQLPooledExecutor executor = new SQLPooledExecutor(          
        "jdbc:mysql://192.168.1.3:3306/test?user=root&password=admin&characterEncoding=utf8", 3, true);

    private static void insert(String s) throws Exception {
        System.out.println("insert " + s);
        executor.execute(conn -> {
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO test(name) VALUES(?)")) {
                ps.setString(1, s);
                ps.executeUpdate();
            }
        });
    }

    public static void main(String[] args) throws Exception {
        while (true) {
            try {
                insert("A");
                Thread.sleep(5000);
                insert("B");
                Thread.sleep(5000);
                insert("C");
                executor.execute(SQLExecutor.COMMIT);
                Thread.sleep(5000);
            } catch (RestartTransactionException e) {
                System.out.println("restart");
            }
        }
    }
}
```
  After the program starts, restart the database at any time point, the following results can be gotten.
  
  insert A
  
  insert B
  
  insert C
  
  restart
  
  insert A
  
  restart
  
  insert A
  
  restart
  
  insert A
  
  insert B
  
  Three restarts occurred because the connection pool size = 3 and round-robin allocated, which means that the two insert A after the first restart triggered the recovery of subsequent connections.
  
  It can be seen that the transaction integrity of the ABC insert database is always guaranteed regardless of restarting the database in any way.
  
  It should be noticed that executor.execute(SQLExecutor.COMMIT); should be in the same try block as other SQL operations, and should not be put into the finally block, because this operation may also throw an exception.

7.16.5 Advanced topic

1. The SQLConnectionConsumer constrains the application implementation and also constrains better transaction integrity.

2. Since the operation may restart, the restartable code must be designed, such as some parameters can not be changed while multiple executed.

3. The SELECT operations of some large result sets perhaps allow the failure in design. In this case, it should be judged whether the operation is restarted and the corresponding decision is made.

4. If the database server is maintained for a long time, the application may appear to be unresponsive. In this case, the logger output should be reviewed or check the connection status.

5. RestartTransactionException is derived from RuntimeException, not SQLException, which is determined by the application complexity. Perhaps most of the methods in the calling stack has nothing to do with SQL. Please refer to the use in limax.zdb.Checkpoint.java.

Prev Next

7.16 JDBC Connection pool

7.16.1 Fundamental

7.16.2 Programming interface

7.16.3 Function scope connection pool

7.16.4 Thread scope connection pool

7.16.5 Advanced topic