CODEWITHSUNDEEP
javamysqlmultithreading
Jury A
Jury A

Reputation: 20082

MySQL Database non-integrity when using Java multi-threading

I have a program that needs multi-threading in Java. The Main function make a query to MySQL DB to select a value name and send one name at a time to each thread.

Each thread will make operations that rely on this name. The run function calls a method say, method1 in Class1, this method perform some operations & insert values in the DB in table2, also, it calls another method, method2 that does operations and insertions in table1.

The DB contains table1 & table2, one-to-many. But I have not connected the tables yet, they are separated till now. But there is a column in table2 that reflects to which record in table1 a certain record in table2 belongs. My DB in table2 are correct, but in table1 are wrong (ex: the first 3 records are not inserted, while I found the related records to them in table2 are inserted, or some times wrong values for another record inserted in else record, etc).

I assigned the name value in the thread constructor. This concerned part in the Main class looks like:

      PreparedStatement Stmt1=null;   //for the first table
      PreparedStatement Stmt2=null;  //for the second table
      private static Statement statement = null;
      private static ResultSet resultSet = null;
      String name=null;
      String query1=null, query2=null;

try {
      DBConnection.ConnectDB(); //connect to database

      query1 = " insert into schema1.table1 values
                 (default,?,?)";

      query2 = " insert into sechema1.table2 values (default,?,?)"; 


          Stmt1 = DBConnection.con.prepareStatement(query1);
          Stmt2 = DBConnection.con.prepareStatement(query1);

          statement = DBConnection.con.createStatement();   
          resultSet = statement.executeQuery("select name from schema1.table1"); 

          ExecutorService threadExecutor = Executors.newFixedThreadPool(10 );

        while(resultSet.next()) 
           {
           myname=resultSet.getString("Column1");
              MyRunnable task1 = new  MyRunnable( myname);
              threadExecutor.execute( task1 );
              nameCounter++;
           }


 threadExecutor.shutdown();

// Wait until all threads are finish
while (! threadExecutor.isTerminated()) { }

System.out.println("Finished all threads");

DBConnection.con.close();

}// end try 
catch (Exception e) {
            e.printStackTrace();
            }

The MyRunnable class is as following:

public class MyRunnable implements Runnable{
private String threadName=null;
private int threadCounter; //to count how many names we have selected

    MyRunnable2 (String name)  

   { 
      synchronized (this){  //I used synchronized as I want to avoid two threads 
                           //taking  the same value.

        this.threadHostName=name;
        this.threadCounter=Main.nameCounter; } //end synchronize
   }     


    public void run() 
    {

       // Here I make a call for the Class1 method1 that I want the thread to perform 

    } //end run()
} //end class MyRunnable


public void Class1 {

       public void method1(String name) 
        {
                  //some calculations

                 //I used synchronized to make the DB records correctly inserted 
                 synchronized(this) { // 
                      for (int i = 0; i < Value.length; i++)
                      {

                        //here I need to insert values in table 2

                         try {
                            synchronized(this){
                Main.Stmt2.setString (1, string1);
                                Main.Stmt2.setString (2, string2);
                                Main.Stmt2.executeUpdate(); 
                              }
                       catch (Exception e)
                        {
                           System.out.println("DB_Error:_"+ e.toString());
                         } 
                      } //end for

                    // Here I made a call for method2 in Class2
                } //end synchronized
               } //end method1
        } //end class1

In Class2, I also need to insert records to the DB, I did the same way in Class1. Calss2 already supposed to be synchronized as it is inside the synchronized Parentheses in Class1. I am completely new to deal with multi-threading.

What is wrong in my code structure ? Am I correct in surrounding Main.Stmt2.setString and Main.Stmt2.executeUpdate() with synchronized ?? How can I maintain DB integrity when dealing with multi-threadings?

Upvotes: 0

Views: 494

Answers (1)

ali haider
ali haider

Reputation: 20232

Have you tried using transactions to ensure that data is in both tables or in neither?
If any existing two phase commit options do not work, either use one thread to do the writes or implement your own two phase commit i.e. thread t3 signals threads t1 and t2 if they are ready to commit. Both threads t1 and t2 must reply with a yes or no response. If the response is yes from both, thread t3 can tell both threads t1 and t2 to proceed with the commit or else tell them not to proceed with the commits/use rollbacks. I have not given this too much thought - one could probably avoid using a third thread and use a data structure shared by both threads to store whether both threads are ready to commit or should rollback the transactions.

Upvotes: 0

Related Questions