Distributed Application-start with database connection pool

This article will mainly introduce one thing-database connection pool (database connection pool). Although we only talk about such a thing, it can lead to an important reason for distributed applications-Why distribution? The purpose of this article is to understand the connection pool and the reason for distribution. Start:

We designed a C/S application, and the use of the logic layer technology to divide the code into a UIL-BLL-DAL, ha ha cool is not, that of course we object-oriented, design patterns that is quite good. The system design diagram is like the following:

 

 

The server here is a simple SQL database server. Each client connects to it to read and write data to complete their own applications. This is also the most classic C/S 2 tiers distributed application in 1980s. In fact, there was no BLL at that time, and the era of Data rad added to the interface was also popular in VB and dephi. Such a structure is barely distributed, and the server only stores data. Basically, there is no business logic. To use the stored procedure in advanced aspects, the business logic is distributed, this is one of the reasons why the stored procedure became popular during that time.

Through this figure, we can quickly find a serious problem. Now there are only three clients. What if there are dozens, hundreds, or even thousands of clients connecting to the database at the same time? You must know that MSDE only supports five concurrent connections. Does MSDE only support five users? Obviously, this is not the case because five users may not connect to the database at the same time. Assume that there are six users, A, B, C, D, E, and F, when a user does not read or write the database, the connection is closed, so that f users will not be able to use it, so they follow good programming habits (learning the dispose mode) and form a good habit of closing the database at will. You can make full use of these five connections to serve dozens or even 20 users.

 

1 using (sqlconnection conn = new sqlconnection (connstr ))

2 {

3 conn. open ();

4 // read write DB

5}

 

It's easy to open, operate, and close. Does this solve the problem? In this seemingly simple process, do you know how hard it is for database comrades? Connecting to the database requires not only communication and memory resources, but also user authentication and security context configuration tasks. Establishing a database connection is definitely one of the most costly operations in the system. What should we do? Distributed architecture was officially launched... Dangdang!

Distribution of the Dal to the server and the use of the connection pool can solve the above problems, as shown in:

Why does this solve the problem? We know that database connections are limited resources and there are n connections. At the same time, we support a maximum of N users to access the database at the same time. This will not change. However, in reality, N users are unlikely to access the database at the same time. Therefore, N connections often support 2n, 3n, or even 10n users. To achieve this goal, the user must release the connection immediately after using it for other users to use it. However, as mentioned above, the cost of establishing a connection is very high. If the client frequently establishes and closes the connection server, it will bring a huge burden to the server. The best way is to reuse the connection, that is, the connection established by Client A is no longer closed and can be directly used by client F. When database connections are owned by different users (when the distributed database is not used and the Dal database is in the client domain), connection reuse cannot be achieved. When the dal is in the server domain, reuse becomes a very simple task. Create a connection pool and retrieve one from it when needed, and put it back when not needed, so that everyone can make full use of the limited connections to serve customers n times. In this case, you do not need to open and close the connection frequently, and the server is much more comfortable.

Story

In the book <COM and DCOM> of Roger sessions, the connection pool technology has a very vivid metaphor.

Use taxi to send passengers from the city center to the airport.
Method 1: Every passenger has a car. Soon the parking lot and road of the airport are full of cars.
Method 2: Each passenger has a vehicle. When the vehicle arrives at the airport, the vehicle is destroyed. As long as your car company's production speed increases with the speed of passengers, and you have enough money, that's okay.
Method 3: When a passenger gets to the airport, he turns to pick up other passengers.

Q: Do all connections use one connection pool?

A: The connection is stored in different pools according to the connection string.

Q: What will happen if I use the connection pool in the client domain?

A: This is not only beneficial, but also harmful. This answer seems a little amazing, but it is not hard to understand it after learning about the working principle of the connection pool. When the connection pool is placed in the server domain, it can be used repeatedly by various clients. When the client domain is used, it cannot be reused by other clients. In this case, using it will not be of any benefit. When the connection is put into the pool, the connection is not closed, which means that even if the client does not read or write the database, it also occupies a limited connection.

This issue is especially noteworthy in. net. sqlconnection uses the connection pool by default. If your dal is deployed on the client, you should disable the connection pool as much as possible. Add pooling = false to the connection string. Of course, Ms will not make such a fatal error. When you do not explicitly set the pool size or set the pool min to 0, when you put the connection into the pool, if it is not used in a few seconds, the system will automatically close the connection, so the problem is not too big.

Q: How do I view database connections?

A: Control Panel-> Administrative Tools performance-> performance right-click on the graph to add a counter and select database connection

　　　

The content in this article is very basic, but you can understand why we need to distribute the Dal, thus starting the evolution of the distributed.

References:
Microsoft. NET Distributed Application
Expert one-on-one J2EE design and develop
Pattern Oriented Software Architecture-patterns for resource management

Related information:
Applying distributed application in. Net Series