After a little bit of research along with help from some of the answers here: SELECT 1 H2 MySQL Microsoft SQL Server (according to NimChimpsky) PostgreSQL SQLite Hive SELECT 1 FROM DUAL Oracle SELECT 1 FROM any_existing_table WHERE 1=0 or SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS or CALL NOW() HSQLDB (tested with version 1.8.0.10) Note: I tried … Read more
It can stay open while connection pooling applies. Example: command timeout can leave locks and TXN because the client sends as “abort”. 2 solutions: Test in the client, literally: IF @@TRANCOUNT <> 0 ROLLBACK TRAN Use SET XACT_ABORT ON to ensured a TXN is cleaned up: Question 1 and Question 2 I always use SET … Read more
Assuming that it’s the BasicDataSource is from DBCP, then yes, you are using a connection pool. However, you’re recreating another connection pool on every connection acquirement. You are not really pooling connections from the same pool. You need to create the connection pool only once on application’s startup and get every connection from it. You … Read more
There is not only one validationQuery for all databases. On each database you have to use different validationQuery. After few hours of googling and testing I have collected this table: Database validationQuery notes hsqldb – select 1 from INFORMATION_SCHEMA.SYSTEM_USERS Oracle – select 1 from dual DB2 – select 1 from sysibm.sysdummy1 mysql – select 1 … Read more
When using Connection Pool, should one close the Connection at the end? If so, isn’t the purpose of pooling lost? And if not, how does the DataSource know when a particular instance of Connection is freed up and can be reused? I am a little confused on this one, any pointers appreciated. Yes, certainly you … Read more
It is apparent that you are using a single, global connection, and apparently leaving it open. As has been mentioned, you should not reuse or store your connection. Connections are cheap to create and .NET is optimized for creating them as needed. There are a number of things in your code which are not being … Read more
The exception indicates a typical case of application code which leaks database connections. You need to ensure that you acquire and close all of them (Connection, Statement and ResultSet) in a try-with-resources block in the very same method block according the normal JDBC idiom. public void create(Entity entity) throws SQLException { try ( Connection connection … Read more
There is no connection pooling in php. mysql_pconnect and connection pooling are two different things. There are many problems connected with mysql_pconnect and first you should read the manual and carefully use it, but this is not connection pooling. Connection pooling is a technique where the application server manages the connections. When the application needs … Read more
DBCP is out of date and not production grade. Some time back we conducted an in-house analysis of the two, creating a test fixture which generated load and concurrency against the two to assess their suitability under real life conditions. DBCP consistently generated exceptions into our test application and struggled to reach levels of performance … Read more
If you need a standalone connection pool, my preference goes to C3P0 over DBCP (that I’ve mentioned in this previous answer), I just had too much problems with DBCP under heavy load. Using C3P0 is dead simple. From the documentation: ComboPooledDataSource cpds = new ComboPooledDataSource(); cpds.setDriverClass( “org.postgresql.Driver” ); //loads the jdbc driver cpds.setJdbcUrl( “jdbc:postgresql://localhost/testdb” ); … Read more