How to verify that connection pooling is working

Question

I have set up connection pooling in my Tomcat configuration, but now I want to verify that it is actually working.

Is there a way to dump out some sort of ID of the active connection so that I can verify the same one is being used between requests? I have checked Oracle's Connection Documentation but to no avail.

Thanks in advance!

Have you considered [enabling JDBC logging](http://stackoverflow.com/questions/27060563/enable-logging-for-jdbc)? — Elliott Frisch, Sep 20 '15 at 18:35
A connection pool contains several connections. There is no guarantee to always get the same connection between requests. And BTW, you could also always get a new Connection object wrapping the actual, reused, physical connection. You could just print the class name of the connection you're getting. It should be a class from your pool instead of a class from the JDBC driver. — JB Nizet, Sep 20 '15 at 18:36
Have you looked at below link ? http://stackoverflow.com/questions/16395608/logging-connection-pooling-for-org-apache-commons-dbcp-basicdatasource-with-spri — Vasu, Sep 20 '15 at 18:36

DuncG · Answer 1 · 2021-12-22T19:36:30.033

1

A simple way to check pool members are re-used: If your JDBC vendor is using the standard toString from Object you should see the same values printed when you print the connection:

System.out.println("Connection="+conn);

If this changes each pool get call, then the connection is not the same as before. However this may not help you at all if your DataSource is wrapping a pooled connection each time with it's own handler class - typically done to make close() return to DataSource and keeps the underlying Connection open.

If your JDBC vendor has not used standard toString() you can make your own string to use in debug / test statements:

public String toString(Connection conn) {
    return conn.getClass().getName() + "@" + Integer.toHexString(conn.hashCode());
}

System.out.println("Connection="+toString(conn));

Note that the above approach does not guard against rogue code changing elements of the Connection or leaving it in in-determinate state. For example I've seen: altered auto-commit modes, selecting another default database database schema (Sybase), not committing the previous transaction!

For some DBs you can mitigate with a test query before use but this incurs an overhead.

edited Dec 22 '21 at 19:36

answered Dec 22 '21 at 18:43

DuncG

12,137
2
21
33

so each connection i make with datasource.getConnection() is creating a new pool ? – Vinayak Dec 23 '21 at 03:06
No, what I'm saying is that the Connection definition of a JDBC implementor might be wrapped as a thin Connection provided by the DataSource implementor in order to help assist housekeeping operations in the pool. Whether the Connection a new wrapper, or same wrapper, or the actual Connection is down to the DataSource implementor. – DuncG Dec 23 '21 at 08:19
How can I basically check if the pool is working ? I have configured it in the context.xml of tomcat server. I need a understandable code snippet that can be used to test the working of pool. – Vinayak Dec 23 '21 at 15:18
I think your question is slightly different: Verifying that the pool is working means confirming that the second use (assuming no concurrent access) gives back same (single) JDBC connection. That is different to verifying that the Connection works, which can be confirmed by a test query. Example for Oracle could run `select null from dual` or Sybase `select user()` or something like that. – DuncG Dec 24 '21 at 11:35

score 0 · Answer 2 · answered Sep 20 '15 at 18:52

0

Simple check would be

SELECT SID, SERIAL# FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID')

if your pool size is 1 you will get the same values from any connection object. If your pool size is greater (it also depends if you have fixed pool size or if it is set to grow when needed) and you have many active connections at the same time you should get up the pool size number of distinct twins.

If the connection is non-pooled creating and opening a new connection object will every time return different values.

answered Sep 20 '15 at 18:52

Husqvik

5,669
1
19
29

This looks like what I was hoping for, but I have to admit I have no clue what that code is or how to use it. It doesn't look like Java and it doesn't look like Bash. Am I missing something? (Is this SQL? If so I was completely unaware that it had that functionality) – John Smith Sep 20 '15 at 19:02
This is SQL. Execute it via the java connection and get back the values. Another option is to watch the records from V$SESSION in the database while your application is connected, for example using SELECT SID, SERIAL# FROM V$SESSION WHERE PROGRAM = ''. – Husqvik Sep 20 '15 at 19:05

score 0 · Answer 3 · answered Sep 21 '15 at 06:33

There's a simple answer and one that makes more work: If you configure a connection pool and don't explicitly open a connection to your database anywhere in your code, the mere nonexistence of manual connection creation should be a clue that something in your connection pool works.

As the connection pool comes from Tomcat, it will also be contained in the data that you can tap into through JMX - enable JMX and connect with your jconsole. This will give you information about the exact load (used connections, free connections, pre-allocated connections) of your connection pool any time you look.

How to verify that connection pooling is working

3 Answers3