Does Mysql execute statement synchronously in one connection?

Question

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/","root", "password");

Let's say in Java, we can create a mysql connection via the code above. From the connection object, we can create few statement objects as below:

statement = connection.createStatement();

I would like to know, if we execute those statement object (by calling statement.executeQuery) in different threads, will they execute synchronously or asynchronously in Mysql database? Because what I know is, one connection in mysql will be handled as one thread, so my thinking is, all the statements that are created by that connection will schedule in its queue. Am I correct?

So, if I have a servlet like below:

public class HelloServlet extends HttpServlet {

   Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/","root", "password");  


   public void doGet(HttpServletRequest request, HttpServletResponse response)
               throws IOException, ServletException {

    statement = connection.createStatement();
   }
}

From the code above, if there are more than one user connect to the servlet at the same time, will they block each other because the statement cannot execute parallel at the same time? So, they have to wait previous statement finish executing before their turn take on? Any way to avoid this kind of problem?

First, you won't be able to use `getConnection` in the field initialization. Second, use a connection pool and create and use one statement per connection. — Sotirios Delimanolis, Sep 24 '13 at 15:45
@SotiriosDelimanolis even if you do this, OP's not in the right approach. — Luiggi Mendoza, Sep 24 '13 at 15:46
@SotiriosDelimanolis the `Connection` object must be in atomic operations, this means it should not be shared among multiple threads, which is indeed wrong. You can find more info here: http://stackoverflow.com/q/3106452/1065197, specifically in the **Threadsafety** section (emphasys taken from BalusC's answer). — Luiggi Mendoza, Sep 24 '13 at 15:49
@LuiggiMendoza What I meant with the connection pool comment was to acquire and use the connection in the handler method (doGet, doPost, etc.). Unless OP shares that `Connection` object in there, they will be fine. — Sotirios Delimanolis, Sep 24 '13 at 15:51
@SotiriosDelimanolis yes I agree with that part, but `Connection` **must not** be a field of a Servlet, instead a local variable in the necessary method (not even in the `doGet` nor `doPost` methods =\\). — Luiggi Mendoza, Sep 24 '13 at 15:52
@LuiggiMendoza We're on the same page. And you've made it clearer for OP! — Sotirios Delimanolis, Sep 24 '13 at 15:53
HI, @SotiriosDelimanolis, can tell why "you won't be able to use getConnection in the field initialization"? — Sam YC, Sep 25 '13 at 08:38
@GMsoF Try it and see. `DriverManager#getConnection()` is declared as throwing an `SQLException`. A thrown checked exception cannot be handled by the default no-arg constructor. — Sotirios Delimanolis, Sep 25 '13 at 12:28

score 5 · Accepted Answer · answered Sep 24 '13 at 15:48

Because what I know is, one connection in mysql will be handled as one thread, so my thinking is, all the statements that are created by that connection will schedule in its queue.

You should not hold a database connection open to the database. You should use a connection pool like Apache's DBCP and get a connection, execute your query or other SQL, and then release the connection to the pool.

if there are more than one user connect to the servlet at the same time, will they block each other because the statement cannot execute parallel at the same time? So, they have to wait previous statement finish executing before their turn take on?

Right. If you have one user making a database transaction, another thread cannot use the same connection to the database at the same time.

public class HelloServlet extends HttpServlet {
     Connection connection = DriverManager.getConnection("j...");

Yeah, you should not create a connection as a field to your servlet class like this. It will open a TCP connection to the database and hold it for the life of your application. These connections sometimes time out or are closed because of network issues so need to be kept alive and on occasion reopened. All of this is handled by DBCP or other connection pools.

+1. IMO would change the *should not create a connection like this* for a *must not*. — Luiggi Mendoza, Sep 24 '13 at 15:53

score 2 · Answer 2 · answered Sep 24 '13 at 15:45

2

If you are using multithreading then each thread should have its own connection. If threads share connections then it becomes a single threaded application as threads will not be able to work simultaneously.

answered Sep 24 '13 at 15:45

Lokesh

7,810
6
48
78

score 0 · Answer 3 · answered Sep 24 '13 at 15:50

Besides weather you are able to create a connection like that or not,Servlet instance member's are not thread-safe.So when you use connection it in servelt as instance member,definitley you are inviting problems.

Go for a connection pool, and make a instance for thread.

Does Mysql execute statement synchronously in one connection?

3 Answers3