Absurd Java MultiThreading and MySQL syncrhonization

Question

Related to the question: Java Threading with MYSQL puzzled

The counter int, is synchronized, the function calls to executeQuery are not synchronized. Results I am getting

counter: 1

row from MYSQL: 1

counter: 2

row from MYSQL: 1

counter: 3

row from MYSQL: 1

Expected Output-> row from MYSQL: should be the same as counter

All the threads run at the same time the first query. Hence they get the same id of the result. However they synchronize for the counter iteration. To me this is absurd. Is this behavior documented? How can I bypass it? Verifier.java

public void run() {

int id = 0;
    String query = "SELECT ID_num FROM Searches WHERE checked='0'";
    ResultSet results;
    synchronized (this.database) {
        results = this.database.executeQuery(query);
        if (results.next()) {
            id = results.getInt(1);
            query = "UPDATE Searches  SET checked='1' WHERE ID_num='"
                    + id + "'";
            this.database.counter++;
            System.out.println(this.database.counter);
            System.out.println(id);

        }
    }
}

Core.java

for (int i = 0; i < verifier.length; i++) {
            MySQL database=new MySQL();
           verifier[i]=new Thread(new Verifier(database,i+1));  
            verifier[i].start(); 
    }

MySQL.java

public class MySQL{
    int counter=0;
....

}

Your code as it is means your update query, increment and sysout are synchronized on 3 different object locks not 1. — shazin
– shazin, Commented Nov 15, 2013 at 4:38

Lokesh · Accepted Answer · 2013-11-15 05:44:07Z

1

EDIT: I see the issue, you update query is not committing the results. You shld commit after update and you will see that table count will also change.

EDIT2: OP says

Expected Output-> row from MYSQL: should be the same as counter

but with current code this can't be achieved. Following is the logic of program:

Fetch all rows with checked='0' .
Thread will update all the rows fetched with checked='1'
Second thread comes and it tries to find rows with checked='0' which will always be 0 if first thread commits the rows.

In order to achieve this behaviour you should not hard code checked='0' in your query instead that should be derived from thread count [the i value passed to Verifier]

EDIT3: There is a correction here, threads will not execute sequentially as new database is created for each thread. Above points will still hold true but this will make achiving expected result more difficult.

edited Nov 15, 2013 at 5:44

answered Nov 15, 2013 at 4:47

Lokesh

7,9487 gold badges50 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NinjaStars Over a year ago

The counter is just to show sync is actually working. My main problem is that all threads fetch the same row instead of sequential rows.

NinjaStars Over a year ago

Please clarify what you mean by, "You shld commit after update and you will see that table count will also change."

Lokesh Over a year ago

Just executing a query will not commit the results if auto commit is set to false. So either set auto commit to true to you should commit explicitly.

Collectives™ on Stack Overflow

Absurd Java MultiThreading and MySQL syncrhonization

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related