Take the 2-minute tour ×

I have to create an mysql database to be used by several applications in parallel for the first time. Up until this point my only experience with mysql databases have been single programs (for example webservers) querying the database. Now i am moving into a scenario where i will have several CXF java servlet type programs, as well as a background server editing and reading on the same schemas. I am using the Connector/J JDBC driver to connect to the database in all instances.

My question is this: What do i need to do in order to make sure that the parallel access does not become a problem. I realize that i need to use transactions where appropriate, but where i am truly lost is in the management. For example. Do i need to close the connection every time a servlet is done with a job? Do i need a unique user for each program accessing the database? Do i have to do something with my Connector/J objects? Do i have to declare my tables in a different way? Did i miss anything or is there something i failed to think about? I have a pretty good idea about how to handle transactions and the SQL itself, but i am pretty lost when it comes to what i need to do when setting up my database.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

You should maintain a pool of connections. Connections are really expensive to create think on the order of of several hundred milliseconds. So for high volume apps it makes sense to cache and reuse them.

For your servlet it depends on what container you are using. Something like JBoss will provide pooling as part of the container. It can be defined through the datasource definition and accessed through JNDI. Other containers like tomcat may rely on something like C3PO.

Most of these frameworks return custom implementations of JDBC connections that implement the close() methods with logic that returns the connection to the pool. You should familiarize yourself with the details of your concrete implementation to make sure you are doing things in a way that is supported

As for the concurrency considerations, you should familiarize yourself with concepts of optimistic/pessimistic locking and transaction isolation levels. These have trade offs where the correct answer can only be determined given the operational context of your application.

Considering the user, Most applications have one user that represents the application called the read/write user. This user should only have privilege to read and write records from the tables,indices,sequences, etc. that are associated with your application. All the instances of the application will specify this user in their connection string.

If you familiarize yourself with the concepts above, you'll be about 95% of the way there.

One more thing. As pointed out in the comments on the administration side your database engine is a huge consideration. You should familiarize yourself with the differences and the tuning/configuration options.

share|improve this answer
One other note: Use the InnoDB storage engine, not MyISAM. InnoDB is much better at handling concurrency (row locking instead of table locking). –  Keith Randall Apr 25 '12 at 22:12
On the subject on the connection pool, i have a few followup questions. 1. Is it meant to be shared between all servlets or is it fair to give each their own connection pool? The reason is that most of the establihed unit tests runs outside of tomcat, and i dont see them working with a tomcat specific object. 2. From what i have read here, am i correct to assume that the primary concern is to avoid accessing a single connection object in parallel? Having several connection objects tied to the same database user is fine, as is having persistent connections? –  Martin Nielsen Apr 26 '12 at 19:08
1) The connections themselves are typically not shared. The pool is shared and is thread safe. The primary concern of the pool is how long it takes to create a database connection. A much simpler solution is to create a connection on every request, but 99% of the response time would be negotiating that connection. Its much more efficient to negotiate a bunch of connections up front and reuse them. 2)I believe connections are technically thread safe, but its ill-advised to share them in multiple threads because a transaction's commit() is managed by the connection. –  nsfyn55 Apr 27 '12 at 1:31
OVERFLOW: In almost every case you will have one pool per JVM--> Database. So Servlet deployed in cluster with 3 replicated nodes = One Database and 3 connection pools. unless you have a very abnormal setup. That pool would typically be used by everything in the container(the servlets, EJB's, and whatever else might need access to that database). Finally if you are running real unit tests they should never have any reliance things like databases or containers. I'd advise investigating Dependency Injection and a good mocking framework like mockito –  nsfyn55 Apr 27 '12 at 1:34

Your Answer


