Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Sql Connection busy...

Hi, i have a question. If i have two theards, each will sleep for a time,during wake up, the threads will read from Database and update certain fields. In case, both theads update the same record at the same time, Java will catch the exception(connection busy). How i going to use synchronized to control the threads so that one will wait for another to finish update record. Thanks.
0
chencc77
Asked:
chencc77
  • 14
  • 11
1 Solution
 
Venci75Commented:
try this:

synchronized (conn) {
  // operating with the connection
}

where conn is your database connection
0
 
chencc77Author Commented:
where to add it?in run method for each thread?
0
 
Venci75Commented:
enclose the usage of the database connection with this. For example:
synchronized (conn) {
 statement = conn.createStatemt();
 statement.executeUpdate("");
}

This code:
synchronized (conn) {}
means that the enclosed code need the 'monitor' of the 'conn' object. The monitor is released after the code is ececuted. If a thread cannot acquire the monitor - it will sleep until the monitor is released by another thread.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chencc77Author Commented:
where to add it?in run method for each thread?
0
 
Venci75Commented:
if you are using the database connections directly in the run() methods of the threads - then yes.
0
 
chencc77Author Commented:
now...one of the thread is waiting, and another couldn't finish processing. Actually i have a main java program that generate N theard. I only declare a contant of sql(conn) in main.java . izit will course problems?
0
 
chencc77Author Commented:
Both threads is continuous thread. It will wake up, perfome some task, and sleep again waiting for another wake up time.
0
 
Venci75Commented:
It doesn't matter what exactly the threads are doing. The 'synchronized' word is used to lock the object (in this case - the connection) for the code enclosed in this section. You don't need to do any additional steps for sleeping if another thread has locked this object. When the 'synchronized' code must be ececuted - the thread will start sleeping and will wake up when the onject ('conn') is released.
0
 
chencc77Author Commented:
if i haven't synchronize the thread, this error msg will occur : java.SQLException;[Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt.

In my program, main.java generate N threads(task.java) and a Update.java to update the Thread's sleeptime. ok, when i sychronize the run() method for task.java and Update.java. The Update.java will run script before i the start() method, and Update.java will stop here before start() is call. But the task.java work fine.
Hope u can help me with this, thank you very much.
0
 
Venci75Commented:
Ok - can you post some code then
0
 
chencc77Author Commented:
I am using RMI, the code quite long. so i post the sql connection program. In my main.java, i just create a constant, RemoteSQL sql = new RemoteSQL().

import java.rmi.*;
import java.sql.*;
import java.util.Vector;
import java.io.*;
import java.net.*;

import java.text.SimpleDateFormat;
import java.text.MessageFormat;
import java.text.DecimalFormat;
import java.util.SimpleTimeZone;
import java.text.ParsePosition;
import java.util.Calendar;

import java.util.GregorianCalendar;

public class RemoteSQL {
 
        public static int connectionId;
        RmiJdbc servlet;
        String result[];
        public static String strStatus;
        public static String strQueryResult[][];
 
        public RemoteSQL() {
            try {
                    servlet = (RmiJdbc)Naming.lookup("rmi://172.18.18.74/AccessDatabase");
                    connectionId = servlet.openConnection();
            } catch (RemoteException re) {
                    System.out.println( "A Remote Exception was thrown when requesting the HelloService" );
                    System.out.println( "" + re );
            } catch (MalformedURLException mURLe) {
                    System.out.println( "There is a problem with the rmi: URL you are using" );
                    System.out.println( "" + mURLe );
            } catch (NotBoundException nbe) {
                    System.out.println( "" + nbe );
            } catch (SQLException se) {
                    System.out.println( "" + se );
            } catch (IOException ioe) {
                    System.out.println( "" + ioe );
            } catch (ClassNotFoundException ce) {
                    System.out.println( "" + ce );
            }
        }

 
        public Vector performQuery(String str) {
               
                String searchString = str;
                Vector v = new Vector(0,1);
                String result;
               
                try {
                        servlet.performQuery(connectionId, searchString);
                        // Get and display the result set.
                      result = servlet.getNextRow( connectionId );
                        int index = 0;
                        if ( result == null ) {
                                System.out.println("No rows found using specified search criteria");
                                v.clear();
                        } else {        
                          while ( result != null ) {
                                      v.add(index, result);
                                      index++;
                                      result = servlet.getNextRow( connectionId );
                              }
                      }
                } catch (RemoteException re) {
                        System.out.println(re);
                        v.clear();
                } catch (SQLException se) {
                        System.out.println(se);
                        v.clear();
                } finally {
                        return v;
                }
        }
       
        public String[][] loadQuery(String strQuery, int intNumCol) {
        //public int loadQuery(String strQuery, int intNumCol) {

                Vector v = new Vector(0,1);
                String strTemp[][];
                int length;
              /*  try {
                        v = performQuery(strQuery);  
                } catch (Exception e) {
                        e.printStackTrace();
                } */
               
                v = performQuery(strQuery);
               
                if (v.isEmpty()) {
                        return strTemp = null;
                } else {
                        length = v.capacity();
                }
                if (length == 0) {
                        return strTemp = null;
                } else {
                        strTemp = new String[length][intNumCol];
 
                        for (int i = 0; i < length; i++) {
                                v.toString();
                                Object obj = v.elementAt(i);
                                String strRecord = obj.toString();                      
                                int intRecordLen = strRecord.length();
                                int j = 0;
                                int index = 0;

                                while (j < (intRecordLen-1)) {
                                        int intSeparator = strRecord.indexOf("|", j);
                                        String str = strRecord.substring(j, intSeparator);
                                        strTemp[i][index] = str;
                                        index = index + 1;
                                        j = intSeparator + 1;
                                }
                        }
                        return strTemp;
                }                                                    
        }
       
        public boolean insertRecord(String strStatement) {
               
                boolean status = false;
                try {
                        status = servlet.insertRecord(connectionId, strStatement);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                        status = false;
                } finally {
                        return status;
                }
               
         /*       try {
                        servlet.insertRecord(connectionId, strStatement);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                } catch (SQLException se) {
                        System.out.println( "" + se);
                } */
        }
       
        public void closeConnection() {
                try {
                        servlet.closeConnection(connectionId);
                } catch (RemoteException re) {
                        System.out.println( "" + re);
                } catch (SQLException se) {
                        System.out.println( "" + se);
                }
        }
       
        public String getDateTime() {
                try {
                        String strDateTime = servlet.getServerDateTime();
                        return strDateTime;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public String getDate() {
                try {
                        String strDate = servlet.getServerDate();
                        return strDate;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public String getTime() {
                try {
                        String strTime = servlet.getServerTime();
                        return strTime;
                } catch (RemoteException re) {
                        System.out.println(re);
                        return null;
                }
        }

        public int getWeekDay() {

              try
              {
                    return servlet.getServerWeekDay();
              }
              catch (RemoteException re)
              {
                          System.out.println(re);
                        return -99;
              }
        }
}
0
 
chencc77Author Commented:
//this is the main.java

main() {
RemoteSQL sql = new RemoteSQL();
for (i=0, i< N; i++) {
     new task(sql,...);
}

new update(sql,...);

}
   
0
 
Venci75Commented:
I found that the only place where you are using the database connection is your performQuery. In this case - you should synchronize the usage of this connection
       public Vector performQuery(String str) {
               
               String searchString = str;
               Vector v = new Vector(0,1);
               String result;
               
               try {
synchronized (connectionId) {
                     servlet.performQuery(connectionId, searchString);
                       // Get and display the result set.
                     result = servlet.getNextRow( connectionId );
                       int index = 0;
                       if ( result == null ) {
                               System.out.println("No rows found using specified search criteria");
                               v.clear();
                       } else {        
                        while ( result != null ) {
                                     v.add(index, result);
                                     index++;
                                     result = servlet.getNextRow( connectionId );
                             }
                     }
}
               } catch (RemoteException re) {
                       System.out.println(re);
                       v.clear();
               } catch (SQLException se) {
                       System.out.println(se);
                       v.clear();
               } finally {
                       return v;
               }
       }
       
       
0
 
chencc77Author Commented:
Hi, do i still need to synchronized others threads?
0
 
chencc77Author Commented:
connectioId is an int, java.lang.Object is required.
0
 
Venci75Commented:
are the other threads calling the performQuery method of the RemoteSQL  class
0
 
Venci75Commented:
I see - how many servlet objects do uou have?

may be you should use the
servlet
object for synchronization:

synchronized (servlet) {
....

instead of
synchronized (connectionid) {

0
 
chencc77Author Commented:
-->>are the other threads calling the performQuery method of the RemoteSQL  class

Yes.
0
 
chencc77Author Commented:
> how many servlet objects do uou have?

there are two : insertRecord and loadQuery
0
 
Venci75Commented:
Now I think that I undestand what happens. You have only one RemoteSQL object and all the threads are calling its methods. To avoid concurent cals to the methods that are using the database conenction - use the RemoteSQL  object for synchronization by changing the declaration of its methods:
public synchronized Vector performQuery(String str) {
public synchronized String[][] loadQuery(String strQuery, int intNumCol) {
public synchronized boolean insertRecord(String strStatement) {
public synchronized void closeConnection() {
public synchronized String getDateTime() {
public synchronized String getDate() {
public synchronized String getTime() {
public synchronized int getWeekDay() {

0
 
chencc77Author Commented:
Do i need to synchronized those methods in update.java and task.java ?? both will get the remoteSql from main.java.
0
 
Venci75Commented:
no - only the methods in the RemoteSql class
0
 
chencc77Author Commented:
Venci, just a additional question bout the RemoteSQL class, if i stop the program without using closeConnection method, that mean the connection is still alive. In server, i set only allow 50 connections, after few times, connection will over 50 and error will occur. How do i stop the connection even i din use closeConnection method?
0
 
Venci75Commented:
actually - I don't know any good solution to this. You should fina a way to close this connection - for example - create a
public void finalize() {
  closeConnection();
}

method of the RemoteSql class. This method will be called by the carbage collector when it is possible
0
 
chencc77Author Commented:
ok, I din see any connection busy in the program already. I hope it will work fine. Thanks again. ^-^
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now