Implement efficient database connection pooling (with complete code C # and Java implementation) __c#

Related techniques: Connection pool reference count multi-threading C#.net Java

Directory Introduction database connection pool (Connection pool) Working principle connection pool key problem analysis concurrent problem transaction processing connection pool allocation and release connection pool configuration and maintenance key issues reference count how to implement transaction management connection pooling combining code description construction method Start Service start Service Stop Services StopService request Getconnectionformpool release disposeconnection How to update properties how to determine whether a connection is invalid use thread to manage connection pooling threadcreate Threadcheck

Other

Introduction

General database applications generally follow the following steps: The initialization program user input operation on the UI by user action to generate database operations to submit database operations to the database server .... (Repeat 2~4) close application

And this article focuses on the 4th step above. In one step we are often, open the database connection operation database, and finally close the database.
The operation of the database on the server side is very important because the data you are working with is very large. If you frequently create a database connection, closing the database connection frequently can cause inefficiencies or even trigger a program crash.
Maybe we can have another form of operation database, we can open a database connection while the program is running, and make the connection permanent until the program ' dies ', so there's a security risk, and we know that the longer an object exists or the more times it is used, the more unstable it behaves. The instability is due to potential design problems within the object, as well as to the database connection object. We can't guarantee that a connection object can have a problem that doesn't exist. So we are not afraid to take it long enough to occupy memory.
Since there are so many problems, we need something that will help us maintain our database connection-it's the connection pool, there's a lot of connection pool examples on the web, but most of them are simple examples, or the more complex connection pooling principle, There is no example of a complete introduction and implementation of connection pooling. Here's how you make a connection pool yourself.
For shared resources, there is a well-known design pattern: resource pools (Resource pool). The model is to solve the problems caused by the frequent distribution of resources and ﹑ release. In order to solve our problem, we can use database connection pool technology. The basic idea of a database connection pool is to create a "buffer pool" for database connections. Put a certain number of connections in the buffer pool beforehand, and when you need to establish a database connection, simply remove one from the buffer pool, and then put it back when you have finished using it. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool. More importantly, we can monitor the number of database connections through the connection pool management mechanism, and provide the basis for system development ﹑ testing and performance ﹑. The basic working principle of connection pool is shown in the figure below.

How the database connection pool (Connection pool) works

Analysis of key problems of connection pool

1, concurrency problems

In order to make the connection Management Service have the most universality, it is necessary to consider the multithread environment, that is concurrency problem. This problem is relatively well solved because each language itself provides support for concurrency management like java,c# and so on, using the synchronized (Java) lock (C #) keyword to ensure that the thread is synchronized. The use method may refer to, the related literature.

2. Transaction processing

We know that a transaction is atomic, requiring that the operation of the database conform to the "all-all-nothing" principle, that is, to a set of SQL statements, either full or none at all.
We know that when 2 threads share a connection connection object, and each has its own transaction to process, the connection pool is a problem, because even if the connection class provides the corresponding transaction support, However, we are still not sure that the database operation corresponds to that transaction because we have 2 threads that are in the process of transaction. For this we can use each transaction exclusively for one connection to implement, although this method is somewhat wasteful of connection pool resources but can greatly reduce the complexity of transaction management.

3. Distribution and release of connection pool

The allocation and release of the connection pool have a great effect on the performance of the system. Reasonable allocation and release can increase the reuse of the connection, reduce the cost of establishing new connection, and speed up the user's access.
You can use a list for connection management. That is, the connection has been created into the list to be unified management. Every time a user requests a connection, the system checks to see if there are any connections in the list that can be allocated. If so, assign the most appropriate connection to him (how to find the most appropriate connection article will be pointed out in the key issues); If you do not throw an exception to the user, the list of connections can be assigned by a thread dedicated management after I will introduce the specific implementation of this thread.

4, the connection pool configuration and maintenance

How many connections should be placed in the connection pool to make the system perform best. The system can control the connection in the connection pool by setting parameters such as minimum number of connections (minconnection) and maximum number of connections (maxconnection). For example, the minimum number of connections is the number of connections created by the connection pool at system startup. If you create too many, the system starts slowly, but the system responds quickly when it is created, and if you create too little, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections at development time, and it will be faster to develop, and larger when the system is actually in use, because it will be faster for the client to access. Maximum connection number is the maximum number of connections allowed in the connection pool, the specific settings, depending on the amount of access to the system, can be obtained by software requirements.
How to ensure the minimum number of connections in the connection pool. There are both dynamic and static strategies. Dynamic that is, every time the connection pool is detected, if the number of connections is found to be less than the minimum number of connections, then add a corresponding number of new connections to ensure the normal operation of the connection pool. Static is found when the idle connection is not enough to check again.

Key issue reference count

The allocation, release strategy is very important for efficient reuse of connections, and the approach we adopt is a well-known design pattern: Reference counting (reference count). This model is used extensively in the reuse of resources, and we apply this method to the allocation of the connection. For each database connection, keep a reference count to record the number of users of the connection. On the concrete implementation, we further package the connection class to implement the reference count. The packaged connection class provides 2 methods for implementing reference counting operations, one is repeat (is assigned) Remove (is released), and then uses the Repeatnow property to determine how much is currently being referenced, The specific user who refers to the connection will enlist in the connection pool, and finally provides the Isrepeat property to determine whether the connection can use the reference count technique. Once a connection is assigned, the applicant for the connection is registered and a reference count is added, and when it is released it deletes the information he has registered and reduces the number of reference counts at a time.

There is a great advantage in doing so that we can use the connection efficiently, because once all the connections are allocated, we can use the corresponding strategy to pick out an already used connection from the pool for reuse instead of just taking out a connection to reuse it. The strategy can be chosen as needed, and we have 4 strategies to use:

1.connlevel_readonly Exclusive Way
Use an idle actual connection to assign a connection resource, and the resource will not be able to assign its reference to another requester in the connection pool until the resource is released back. If all of the actual connection resources in the connection pool have been allocated, the connection pool will generate an exception and flag the connection pool resource exhaustion, even if the connection pool can be delayed by allocating a referral resource to the connection in that mode.
Example: If an actual connection can be assigned 5 times, then you will lose 4 allocated connections and only one connection resource if you apply for the connection using this mode. The connection pool continues to allocate the remaining 4 opportunities until the resource is freed back to the connection pool.
When you may apply to a transaction when using a connection, you can use that mode connection to determine that you can have exclusive permissions on the connection during the transaction to avoid interference from individual database operation visits.
2.connlevel_high 　 Priority-High
Use an idle actual connection to assign a connection resource, and the resource may assign its reference to another requester in the connection pool before the resource is released back. * Note: This level does not guarantee that the resource will remain independently occupied after allocating it, and use readonely if you want to independently occupy the resource, because when the connection pool reaches a certain time, the resource is allocated repeatedly (reference count) but this time is unpredictable. You can use the connlevel_readonly level if the connection you are requesting is used for transaction processing.
3.connlevel_nonePriority-Medium
Appropriate application of reference counting technology to allocate connection resources.
In this mode, the connection pool is sorted internally according to the number of times the actual connection has been used (-> less), and then the connection resource in the 1/3 location is returned in the result. And the priority-Gaosian and the model do not have the ability to maintain independent ownership of connection resources. You can use the connlevel_readonly level if the connection you are requesting is used for transaction processing.
4.connlevel_bottom Priority-Bottom
Use reference counting techniques to assign connections whenever possible. In this mode, the connection pool is sorted internally according to the number of times the actual connection has been used (-> less), and then select the most used return in the result. This pattern is appropriate for handling less important connection resource requests. And the priority-Gaosian and the model do not have the ability to maintain independent ownership of connection resources. You can use the connlevel_readonly level if the connection you are requesting is used for transaction processing.

The above 4 policies are chosen from DATEBASEPOOL_SDK (Datebasepool is the final product developed in this article) how to implement transaction processing

All that has been said about using database connections for normal database access. For transaction processing, the situation becomes more complex. Because the transaction itself requires the assurance of atomicity, it requires that the operation of the database be in accordance with the "all-all-nothing" principle, that is, either complete or do nothing. If you simply use the above strategy for connection reuse, there is a problem, because there is no way to control the actions of multiple database manipulation methods that belong to the same transaction, which may be done on multiple connections, and these connections may be reused by other non transaction methods.
Connection itself provides support for transactions, see connection MSDN for explicit invocation of commit or rollback methods for implementation. However, it is necessary to provide the corresponding transaction support mechanism for the connection to be reused safely and efficiently.  We use the method is: the user in the connlevel_readonly mode to apply for a connection after the connection by the applicant alone to enjoy the connection, the specific transaction operations by the user to write their own design, connection pool to provide users alone possession. Managing connection Pooling

As we said above, this connection pool internal connection management uses separate thread work (Threadcreate and Threadcheck) Threadcreate threads are responsible for creating connections, and Threadcheck threads are responsible for checking whether each connection reaches its own lifetime, The condition for marking the connection life is that the number of references is greater than the maximum number of citations, or the maximum lifetime is reached. These parameters are managed by the Connstruct class, which is the class that wraps the connection, and the following defines the property variable (C # code)//property that the connection pool uses
private int _realformpool; Actual number of connections present in the connection pool (including failed connections)
private int _potentrealformpool; Actual number of connections present in the connection pool (valid actual connection)
private int _sparerealformpool; Idle actual connection
private int _userealformpool; Actual connections that have been allocated
private int _readonlyformpool; How many read-only connections have been allocated to the connection pool
private int _useformpool; Number of connections already allocated
private int _spareformpool; The number of connections that can currently be provided
private int _maxconnection; Maximum connections, number of connections that can be created
private int _minconnection; Minimum number of connections
private int _seepconnection; The number of connections each time a connection is created
private int _keeprealconnection; The actual idle connection reserved to attack possible readonly use, and the connection pool creates seepconnection connections when the idle connection is insufficient for this value
private int _exist = 20; 20 minutes per connection lifetime
private int _maxrepeatdegree = 5; can be reused (reference count), the connection will not be assigned when the connection is repeatedly assigned the number of times the value is represented
When the connection pool connection is allocated, the connection pool repeats the connection (reference count) in the connection that is already allocated. To relieve the connection pool pressure.
Private DateTime _starttime; Service Start time
private string _connstring = null; Connection string
Private Conntypeenum _conntype; Connection Pool Connection Type
Private Poolstate _ps; Connection Pool Status
Internal objects
Private ArrayList Al_all = new ArrayList (); Actual connection
Private Hashtable Hs_useconn = new Hashtable (); Connection in use
Private System.Timers.Timer time; Monitor Timer

Private Thread threadcreate; Creating Threads
private bool Isthreadcheckrun = false;

Java version     //Properties
     private   int  _RealFormPool; //Company Actual number of connections present in the pool (including failed connections)
     private   int  _PotentRealFormPool; //Connection pool actual connections present (Valid actual connection)
     Private   int  _SpareRealFormPool; //Idle actual connection
      private   int  _UseRealFormPool; //Assigned actual connection
     private   int   ;_readonlyformpool;