DB connection without active connection?

I just want to ask how the connection pool really works. As far as i can understand, the a connection is fetch from the pool when a DB access is required. The connection pool also has a max limit of connection(depending on what the developer specified), in my case i didn't specified any, BUT i when i printed the maximum active connection it was 8, and so with maximum idle with a waiting time of -1. i believe this is the default since I haven't specified any.

the reason i want to know is because I am maintaining a system in spring, and it updates a database twice for a single transaction(which it was designed to do).I printed the Maximum number of active and Idle,the number of actual active and idle. here is the result:

*1st update

1. max active:8 max idle:8 max wait:-1
2. num idle:1 and active:0 *the con is not fetched from the pool
3. num idle:0 and active:1 *con fetched with dataSource.getConnection()

*2nd update

1. max active:8 max idle:8 max wait:-1
2. num idle:0 and active:0
3. num idle:0 and active:0

So i am wondering, If there are no active connection on the second update, is the connection from the pool different from the connection to the database?

I am really not that knowledgeable about this and i will appreciate your guidance and time.开发者_如何学运维

A request for a non-pooled connection is fairly heavy weight: you must create a new connection, use it, and close it.

A connection pool eliminates most of the overhead of this, as most requests will not incur the cost of creating a connection (the heaviest part of connection management) or the cost of closing it. However, every request to the database will use a connection (so your 2nd update definitely is using a connection). All the pool does is create the connections for you on your behalf, and allows you to reuse them - so the "database connection" and the "pool connection" are really the same thing.

Typically you configure a pool with a minimum number of connections. For example, you might configure it to always have at least five connections. When your application starts up, the pool would allocate these connections before any requests come in. As each request comes in, the pool loans out an already open connection for use by the request. When done using the connection, it should be returned back to the pool by the code that borrowed it. If your application has many concurrent requests coming in, you can run out of connections. This is where other policies on the connection pool come in.

There is typically a setting for what to do when you run out of connections (all connections are loaned out to requests) for example "create 1 more connection" or "create 5 more connections". So if there are only 5 pooled connections and a sixth concurrent request comes in, there will probably be some cost incurred while the pool creates one or more additional connections. However, once created, you now have a pool that has grown to quickly accomodate lending out a larger number of connections for a busy period.

Similarly, if the number of requests coming in has slowed down (perhaps its night time) you might now have a bunch of idle connections sitting in the pool. Pools typically have a configurable policy for what to do with idle connections. For example, you might say you only want at most 5 idle connections in the pool and you consider a connection "idle" if it has not been used in the last 5 minutes. The pool would then close connections to shrink the pool down to a smaller size, freeing up resources that are not needed.

Another policy you can set is the maximum number of connections the pool is allowed to have. In your example you have this set to 8. What this means is no matter how busy your application is, you will never allow more than 8 concurrent database requests. This is where another policy comes in: what to do when you run out of connections and aren't allowed to grow the pool. Typically a pool provides several choices, for example "wait for a free connection", "make more connections", "throw an exception", etc. Since your example has a "max wait" of -1, that must mean the default of your pool is to wait for a connection for as long as it takes (assuming -1 means "forever"). Depending on your app this might be a good or bad choice.

In general, I think over time you monitor how long requests take, how many connections you have at any one time, etc. and tweak your pool settings to be the most efficient, i.e. minimize wait time for creating/borrowing a connection, minimize the number of resources allocated for connections, and react quickly to changing request patterns.

Lastly, regarding your question, you could have 0 idle and 0 active connections if your pool is configured to aggressively close connections. Try setting the minimum pool size to increase this.