0

Is it necessary to synchronize input and output statements on MySQL?

Database

   public class Database {
            private Object locker = new Object();

            public boolean createUser(String username, String password) {
                synchronized (locker) {
                  //INSERT statement

                }
            }

            public boolean isValidLoginAndPassword(String login, String password) {
                synchronized (locker) {
                    //SELECT statement
                }
            }
        }
Piotr K.
  • 95
  • 1
  • 2
  • 9
  • No idea what you are doing here, and there is nowhere near enough information to say if its wrong. Most likely, it's misguided - you should be looking at transactions. – Boris the Spider Apr 20 '16 at 16:46
  • P.S. I really hope you're storing your passwords hashed! And using `PreparedStatement`. – Boris the Spider Apr 20 '16 at 16:47
  • https://github.com/Cezikos/shiny-enigma/blob/master/src/Server/Database.java – Piotr K. Apr 20 '16 at 16:48
  • 1
    Oh my good god - this is horrible. There are so many things wrong that synchronization is the least of your worries - for example [SQL injection](https://xkcd.com/327/). At least you're hashing your password though, so that's a positive. – Boris the Spider Apr 20 '16 at 16:49
  • I think for your particular use case that synchronization on an object is not necessary and creates overhead you may not want. In the general case if two users are withdrawing from a single bank account and you want to make sure a race condition does not create an overdraft then you could explore synchronization token strategies at the database level. – mba12 Apr 20 '16 at 16:50
  • @mba12 otherwise known as transactions. – Boris the Spider Apr 20 '16 at 16:51

1 Answers1

1

The only time you need synchronized is to enforce thread-safety. Other questions have tackled the question of Is java.sql.Connection thread safe? . The short answer therein is that mostly, no, you don't need thread synchronization - JDBC spec is thread-safe. However this varies by sql driver, so you may not actually be threadsafe, but there are libraries to help alleviate this (like Apache Commons connection pools).

But the larger question that you may want to ask is that even if they are threadsafe, what happens when two threads try to use the same Connection? One of them will have to wait, which is usually undesirable. A connection pool (or at least a worker queue) makes the most sense if you have multiple threads doing the same work: you have much less chance of threads being blocked on each other, and you don't need to worry about if the driver is threadsafe.

Community
  • 1
  • 1
Knetic
  • 2,099
  • 1
  • 20
  • 34
  • "you should avoid sharing connections between threads" - If my server create thread for every client(to listen), I should make the same number of connections to the database as number of threads? – Piotr K. Apr 20 '16 at 23:31
  • No - it depends on what your use case is, but generally using a connection pool is a good idea. Connection pools manage multiple connections, but allow you to share them between threads. When a request comes in, you acquire a connection from the pool (it may create a new one for you), then once you're done you give it back o the pool. In that way threads can (without blocking) share connections, usually without 1-connection-per-thread. You might look at questions like http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc to find out more. – Knetic Apr 21 '16 at 00:43
  • I implemented HikariCP, Can you look at the code to check general idea? [Github](https://github.com/Cezikos/shiny-enigma/blob/master/src/Server/Database.java) – Piotr K. Apr 21 '16 at 09:54