Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql Connection busy...

Posted on 2002-06-12
25
Medium Priority
?
244 Views
Last Modified: 2010-03-31
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
Comment
Question by:chencc77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
25 Comments
 
LVL 9

Expert Comment

by:Venci75
ID: 7072672
try this:

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

where conn is your database connection
0
 

Author Comment

by:chencc77
ID: 7072699
where to add it?in run method for each thread?
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7072713
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:chencc77
ID: 7072719
where to add it?in run method for each thread?
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7072727
if you are using the database connections directly in the run() methods of the threads - then yes.
0
 

Author Comment

by:chencc77
ID: 7072818
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
 

Author Comment

by:chencc77
ID: 7072844
Both threads is continuous thread. It will wake up, perfome some task, and sleep again waiting for another wake up time.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7072867
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
 

Author Comment

by:chencc77
ID: 7074329
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
 
LVL 9

Expert Comment

by:Venci75
ID: 7074823
Ok - can you post some code then
0
 

Author Comment

by:chencc77
ID: 7074872
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
 

Author Comment

by:chencc77
ID: 7074876
//this is the main.java

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

new update(sql,...);

}
   
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7074903
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
 

Author Comment

by:chencc77
ID: 7074915
Hi, do i still need to synchronized others threads?
0
 

Author Comment

by:chencc77
ID: 7074920
connectioId is an int, java.lang.Object is required.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7074922
are the other threads calling the performQuery method of the RemoteSQL  class
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7074931
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
 

Author Comment

by:chencc77
ID: 7074932
-->>are the other threads calling the performQuery method of the RemoteSQL  class

Yes.
0
 

Author Comment

by:chencc77
ID: 7074943
> how many servlet objects do uou have?

there are two : insertRecord and loadQuery
0
 
LVL 9

Accepted Solution

by:
Venci75 earned 200 total points
ID: 7074948
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
 

Author Comment

by:chencc77
ID: 7074960
Do i need to synchronized those methods in update.java and task.java ?? both will get the remoteSql from main.java.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 7074978
no - only the methods in the RemoteSql class
0
 

Author Comment

by:chencc77
ID: 7075047
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
 
LVL 9

Expert Comment

by:Venci75
ID: 7075053
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
 

Author Comment

by:chencc77
ID: 7075078
ok, I din see any connection busy in the program already. I hope it will work fine. Thanks again. ^-^
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question