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.