Is C3P0 thread-safe?
An interruption exception (java.lang.InterruptedException) occurs as I'm trying to perform some simple read (SELECT) operations using C3P0 on a MySQL database. The exception occurs as I increase the number of parallel threads more than 100 (I have tried with 5,10,20,60 and 100). The statement I execute is as simple as :
SELECT `Model.id` FROM `Model` LIMIT 100;
My connections are pooled from a ComboPooledDataSource which is configured using the following properties (see also the C3P0 manual):
c3p0.jdbcUrl=jdbc:mysql...
c3p0.debugUnreturnedConnectionStackTraces=true
c3p0.maxIdleTime=5
c3p0.maxPoolSize=1000
c3p0.minPoolSize=5
c3p0.initialPoolSize=5
c3p0.acquireIncrement=3
c3p0.acquireRetryAttempts=50
c3p0.numHelperThreads=20
c3p0.checkoutTimeout=0
c3p0.testConnectionOnCheckin=true
c3p0.testConnectionOnCheckout=true
user=***
password=***
The MySQL server on the machine I run the tests is configured to accept 1024 connections and the unit tests I run are successfully executed (the data are retrieved from the database as expected). However, in the C3P0 log file, I find the following warning:
15:36:11,449 WARN BasicResourcePool:1876 - com.mchange.v2.resourcepool.BasicResourcePool@9ba6076 -- Thread unexpectedly interrupted while performing an acquisition attempt.
java.lang.InterruptedException: sleep interrupted
at java.lang.Thread.sleep(Native Method)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
I'd like to know the reason for that warning and second its possible impact on the software's robustness and stability. Note that after use, I close the result set, the SQL statement and the connection. Finally, once the test is over, I close the pool by calling the method ComboPooledDataSource#close(). What is more weird (and seems to be to reveal a synchronization problem), is that if I give enough time to the pool using the following...
Thread.sleep(10000); // wait for some time
datasource.close();
No warnings will appear in the logs! Dο you think this raises a thread safety issue for C3P0 or am I doing something the wrong way?
Update 1:
Let me mention that removing the Thread.sleep(10000), apart from what already mention, causes the following info to be logged in the MySQL log file:
110221 14:57:13 [Warning] Aborted connection 9762 to db: 'myDatabase' user: 'root'
host: 'localhost' (Got an error reading communication packets)
Might shed some more light...
Update 2:
Here is my MySQL server configuration. The number of maximum allowed connections by server is set to 1024 (as I mentioned above) which is adequate for what I'm trying to do.
[mysqld]
max_allowed_packet = 64M
thread_concurrency = 8
thread_cache_size = 8
thread_stack = 192K
query_cache_size = 0
query_cache_type = 0
max_connections = 1024
back_log = 50
innodb_thread_concurrency = 6
innodb_lock_wait_timeout = 120
log_warnings
To obfuscate any doubt, I verified that the maximum number of connections is properly set by:
show global variables where Variable_name='max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1024 |
+-----------------+-------+
1 row in set (0.00 sec)
Answer
That warning comes from around line 2007 here. It seems to be a thread stuck trying to aquire a connection.
Perhaps because the pool is set up to aquire more connections than what your mysql server is configured to handle. This seems to make sense, as the default max_connection is 100 (or 151 depending on your mysql version)
So that thread trying to aquire a connection goes in to a sleep()/retry loop trying to aquire the connection - however you close the whole pool while it's inside that loop - that thread gets interrupted so all resources can be reclaimed when you close the pool.
So far, it seems no harm done, your code likely returns connections to the pool when you're done with it leaving them idle for others to use, and all your queries get through.