2

I have an application which runs some multi-threading process.
In each thread I'm sharing the same connection to my mysql db which store information important for the thread to run.

Usually when multi-threading I must check for synchronization so I want to share the same resource at the same time, but do I have to do the same with mysql?

I'm using innodb which locks the row when using it, and I don't know if I need to look the access to it in my code too.

To my understanding it doesn't matter if I do it or not because the server it self manage its connection but again I'm not sure.

oers
  • 18,436
  • 13
  • 66
  • 75
Asaf Nevo
  • 11,338
  • 23
  • 79
  • 154
  • 4
    Have you checked out one of the multiple questions dealing with this topic already? For example: http://stackoverflow.com/questions/2167819/java-threads-and-mysql and http://stackoverflow.com/questions/1209693/is-mysql-connector-jdbc-thread-safe – Emil Vikström May 16 '12 at 07:01

1 Answers1

1

Connections in java are not guaranteed to be thread safe.

In addition to that, Mysql (and innodb in particular) can handle multiple connections safely.

In a comment to you question (by Emil Vikström), solutions are proposed to your problem;

(Edit) and I quote:

Have you checked out one of the multiple questions dealing with this topic already? For example: Java Threads and MySQL and Is MySQL Connector/JDBC thread safe? – Emil Vikström

Community
  • 1
  • 1
henryabra
  • 1,433
  • 1
  • 10
  • 15
  • i've actually did read those questions, but as you said, in logical matter i don't understand what are the problems that can happen when using a connection to a shared innodb. and if i am using a shared simple connection between thread, should i synchronized them ? or the mysql knows how to work with them alone.. – Asaf Nevo May 16 '12 at 09:23
  • I have not tried it myself but 2 threads or more using 1 connection object may give undefined results. This is due to the fact that the underlying implementation may maintain the actual connection state (socket, resultSets and etc.). The best practice is to use a connection pool, 1 connection per thread or synchronize same connection usage. So to your question, yes - you should synchronize shared connection. – henryabra May 16 '12 at 10:24
  • Doesnt opening a new connection per thread might overload the mysql server? – Asaf Nevo May 16 '12 at 15:04
  • You should try and experiment all the ways to see what happens. I recommend not doing premature optimization and keeping it simple. You can always look for bottlenecks after you have a concrete implementation. – henryabra May 17 '12 at 07:30