Why do I need to establish multiple connections in the database connection pool?

Generally, a program uses a database connection pool when accessing a database, such as DBCP and c3p0. We all know why the connection pool is used to avoid frequent connections to the database,
Because establishing a connection is a time-consuming operation. The connection pool is basically configured with a maximum number of connections.
Now I have a question: why should I set the maximum number of connections to multiple? Can I create only one connection and never close it? Wouldn't one occupy less system resources (excluding the use of transaction, because the submission and rollback of transaction are on the connection )? Why is Java. SQL. Connection not thread-safe?

Http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec/jdbc-spec.frame9.html

9.2 multi-threading
We require that all operations on all the java. SQL objects be multi-thread safe and
Able to export correctly with having several threads simultaneously calling the same object.
Some drivers may allow more concurrent execution than others.
Developers can assume fully concurrent execution;
If the driver requires some form of synchronization, it will provide it.
The only difference visible to the developer will be that applications will run with specified CED concurrency.
For example, two statements on the same connection can be executed concurrently and Their resultsets can be processed concurrently
(From the perspective of the developer). Some drivers will provide this full concurrency. Others may execute one statement and wait until it completes before sending the next.
One specific use of multi-threading is to cancel a long running statement. This is done by using one thread to execute the statement and another to cancel it with its statement. Cancel () method.
In practice we recommend CT that most of the JDBC objects will only be accessed in a single threaded way. However some multi-thread support is necessary,
And our attempts in previous drafts to specify some classes as Mt safe and some as Mt unsafe appeared to be adding more confusion than light.
That is to say, Java. SQL. connection is thread-safe. Applications can concurrently use the same connection to access the database. The problem is that the database driver may be concurrent requests or serial requests, this depends on the database vendor. At the same time, it is best to use it in a single thread mode in actual development.

Http://docs.oracle.com/cd/B14117_01/java.101/b10979/tips.htm#i1005436

The oracle JDBC drivers provide full support for programs that use Java multithreading.
The following example creates a specified number of threads and lets you determine whether or not the threads will share a connection.
If you choose to share the connection, then the same JDBC connection object will be used by all threads (each thread will have its own statement object, however ).

Because all oracle jdbc api methods are synchronized, if two threads try to use the connection object simultaneously,
Then one will be forced to wait until the other one finishes its use.
Oracle's JDBC APIs are synchronized. Therefore, database operations must be sequential.

Http://forums.mysql.com/read.php? 39,171022, 171195 # msg-171195

While you _ Can _ share a connection established SS threads (especially if each thread has its own statement), it's usually not a good idea.
The jdbc api is not really designed to be used in a thread-safe way, and most JDBC connections (including MySQL's) can only process a single query at a time.
You'll be adding _ more _ contention in your system rather than concurrency compared to just giving each thread a "private" connection.
MySQL connection also performs database operations in a serial manner.

Http://stackoverflow.com/questions/9428573/is-it-safe-to-use-a-static-java-sql-connection-instance-in-a-multithreaded-syste

This way the connection going to be shared among all requests sent by all users and thus all queries will interfere with each other.
But threadsafety is not your only problem, resource leaking is also your other problem.
You're keeping a single connection open during the entire application's life time.
The average database will reclaim the connection whenever it's been open for too long which is usually between 30 minutes and 8 hours, depending on DB's configuration.
So if your web application runs longer than that, the connection is lost and you won't be able to execute queries anymore.
This means that the connection may be recycled if one connection is used without trial for a long time. Generally, the maximum inactive time of a connection is between 30 minutes and 8 hours, depending on the configuration of the database server.

Set MySQL connection timeout parameters

Http://blog.chinaunix.net/uid-26758020-id-3307042.html

In the default settings of MySQL, if a database is not used for more than 8 hours (idle for 8 hours, that is, 28800 S), MySQL server will automatically disconnect this connection, subsequent query operations on the connection will fail,
Error 2006 (MySQL server has gone away) is displayed )!.
View MySQL Server Timeout:
Msyql> show global variables like '% timeout % ';
Set MySQL Server timeout (in seconds ):
Msyql> set global wait_timeout = 10;
Msyql> set global interactive_timeout = 10;
PS: our online server is set to 2 hours.

Configuration parameters interactive_timeout and wait_timeout in MySQL (two parameters that may lead to too many sleep processes)

Http://www.cnblogs.com/jiunadianshi/articles/2475475.html

1) interactive_timeout:
Parameter description: The number of seconds before the server closes the interactive connection. Interactive clients are defined as clients that use the client_interactive option in mysql_real_connect.
Default Value of the parameter: 28800 seconds (8 hours)
(2) wait_timeout:
Parameter description: The number of seconds before the server closes "non-interactive connection.
When a thread starts, the wait_timeout value of the session is initialized based on the global wait_timeout value or the global interactive_timeout value, depending on the client type (defined by the client_interactive connection option of mysql_real_connect ).
Default Value of the parameter: 28800 seconds (8 hours)
The maximum number of connections supported by the MySQL server is limited, because the creation of each connection consumes memory. Therefore, we hope that after the client connects to the MySQL server to process the corresponding operations, disconnect and release the occupied memory. If your MySQL server has a large number of idle connections, they will not only consume the memory in vain, but if the connections continue to accumulate and continue to open, it will eventually reach the maximum number of connections of the MySQL server, this will report the 'too has connections' error. The value of wait_timeout should be determined based on the system running condition. After the system runs for a period of time, you can run the show processlist command to view the connection status of the current system. If a large number of connection processes in sleep status are found, this parameter is set too large, you can make some adjustments.
Problem:
If you only set the parameter wait_timeout = 100 in the configuration file my. CNF, restart the server and run the following command:
Mysql> show variables like "% timeout % ";
The parameter setting is still 28800 (8 hours by default ).
After querying data, you must set interactive_timeout and wait_timeout at the same time.
[Mysqld]
Wait_timeout = 100
Interactive_timeout = 100
After the MySQL server is restarted, check that the settings have taken effect.

Http://blog.csdn.net/z1988316/article/details/7976038

Question 1: Why does the setting of interactive_timeout and wait_timeout take effect at the same time?
A: If interactive_timeout is not set, wait_timeout will also take effect.
Question 2: If the interactive value is set differently than wait_timeout, why does interactive_timeout overwrite wait_timeout?
A: In client_interactive mode, interactive_timeout takes effect. In non-interactive mode, interactive_timeout does not take effect.
Problem 3: During MySQL optimization, interactive_timeout determines the length of interactive connections, while wait_timeout determines the duration of non-interactive connections. If the last client_flag parameter of mysql_real_connect () is not set to client_interactive during connection configuration, will the value of interactive_timeout not overwrite wait_timeout?
A: You can try the experiment.
Question 4: to reduce the number of persistent connections, can I set the value of interactive_timeout to a greater value while that of wait_timeout to a smaller value? However, the description of Question 2 does not seem to allow this...
A: As described in section 2, interactive_timeout replaces wait_timeout in interactive mode. In this way, if some clients connect to MySQL server in interactive mode. Therefore, the client timeout is subject to interactive_timeout. If some clients are in non-interactive mode, they can connect to the MySQL server for a long time. The client timeout is subject to wait_timeout. (Whether the connection is in interactive mode is determined by the client)

Summary:
(1) As mentioned in JDK specifications, connection is thread-safe for clients and allows concurrent use of multiple threads. However, the database driver may be executed in parallel, it may also be a serial execution of SQL.
(2) For the same connection, the drivers of Oracle and MySQL are serial execution operations. Therefore, sharing a connection will reduce the concurrency compared with using multiple connections. This should be the main reason.
(3) The database usually has a maximum inactivity time. When this time is exceeded, the connection will be closed. Therefore, if there is only one connection, the connection will be closed, reconstruction is required. (In fact, this is not a big problem. Start a background thread and execute a query every other time. Keep the Connection closed .)
(4) How many connections are suitable is a problem. If the number of connections is too large, it is obviously a waste, and the number of connections supported by each user in the database is limited. If the number of connections is small and the sudden traffic volume increases, some threads may fail to obtain the connection, and the request will fail. If multiple requests can share connections, at least no connection failure will occur if the connection fails to be obtained. At most, the processing time will be longer.