synchronize of multiple method accesses

Question

I need to synchronize a mysql query, so just one user can access the specified part at the same time. I tried synchronized(){}, but this doesn't work. The users can access the method simultaneously. It's a jsp webapplication using tomcat6. I need this, because the webserver crashes when too many user run the query at the same time.

String sql = "SELECT * FROM products;"
ResultSet rs;

//This block should be synchronized
Statement s = con.createStatement();
rs = s.excecuteQuery(sql);

while (rs.next()) {
// do some stuff..
}

Thanks for any help.

Edit: how I tried to synchronize

public class connect {

public static String URL = "url";
public static String USER = "root";
public static String PASSWORD = "password";
private Object lock = new Object();

public void getData() {

    Connection con;

    try {
        con = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM products;";
        ResultSet rs;

        //This block should be synchronized
        synchronized(lock) {
            Statement s = con.createStatement();
            rs = s.executeQuery(sql);

            while (rs.next()) {
            // do some stuff..
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
}

I think the object lock should be stored on the server. But what's the right way to do this?

your `lock` is `null`. Though i'm not too familiar with synchronized blocks i think it should be an instance and not `null` — Marco Forberg, Jun 07 '13 at 09:26

score 1 · Answer 1 · edited May 23 '17 at 12:06

Your code is ok if the lock object is the same for all requests being processed.

How can you figure it out? If you create a new connect class for each request you are processing, then each instance of this class has a different lock, and the synchronized block can be executed concurrently by the different instances.

If you have only one instance of the connect class, then you have only one instance of lock and you are sure that no two threads can execute concurrently the synchronized block.

However even if you have only one instance of the connect class, it is easy to break the synchronization if a refactoring the code creates more than one instances of this class. If you want to be sure that this code locks for all threads, then you have to declare the lock object as static:

static final Object LOCK = new Object();

Now you are certain that there is only one LOCK in the JVM.

You also have to keep in mind that if your application runs in a cluster, then java synchronization will not work because you have more than one JVMs running the same application. Have a look at this question for more details on the subject.

BTW, try to follow the class naming conventions. Class names should start with a capital, so rename your class to Connect.

nebula · Answer 2 · 2013-06-25T03:49:04.083

1

why don't you just use singleton and synchronized the Connection, or synchronized on the methods

public class MyConnection {

    private Connection mConn;

    public static synchronized Connection getConnection() {
    if(mConn == null){
     mConn = createNewConnection();//your create method
    }
    return mConn;
    }

}

edited Jun 25 '13 at 03:49

answered Jun 25 '13 at 03:38

nebula

382
3
10

score 0 · Answer 3 · answered Jun 07 '13 at 09:10

I don't know how you synchronized the method. But unless the class that contains the method is a singleton it won't work.

Well either that's a singleton or the object you are using for locking the method have to be for this to work.

If you simply synchronized the method multiple instances of the class will thwart your intention.

But it seems to me that there's something wrong with your architecture. Are you using some kind of a connection manager?

score 0 · Answer 4 · answered Jun 07 '13 at 09:13

0

If you're using DBCP then simply configure maxActive attribute which is 100 by defulat. The threads that are trying to get a Connection above the limit will be blocked.

answered Jun 07 '13 at 09:13

Evgeniy Dorofeev

133,369
30
199
275

I've never heard about `DBCP`, but it looks really easy to understand. Maybe I've to implement it in my code. – yves.beutler Jun 07 '13 at 09:46

score 0 · Accepted Answer · answered Jun 26 '13 at 05:57

There was a wrong query statement in another part of my code. If this query was executed, it did something like SELECT * FROM TABLE and this knocked my mysql server out. Now I've fixed it and don't have to limitate the db access. Thanks for your help guys!

synchronize of multiple method accesses

5 Answers5