Solved

Sql Connection busy...

Posted on 2002-06-12
25
230 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
  • 14
  • 11
25 Comments
 
LVL 9

Expert Comment

by:Venci75
Comment Utility
try this:

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

where conn is your database connection
0
 

Author Comment

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

Expert Comment

by:Venci75
Comment Utility
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
 

Author Comment

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

Expert Comment

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

Author Comment

by:chencc77
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok - can you post some code then
0
 

Author Comment

by:chencc77
Comment Utility
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
Comment Utility
//this is the main.java

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

new update(sql,...);

}
   
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:Venci75
Comment Utility
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
Comment Utility
Hi, do i still need to synchronized others threads?
0
 

Author Comment

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

Expert Comment

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

Expert Comment

by:Venci75
Comment Utility
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
Comment Utility
-->>are the other threads calling the performQuery method of the RemoteSQL  class

Yes.
0
 

Author Comment

by:chencc77
Comment Utility
> how many servlet objects do uou have?

there are two : insertRecord and loadQuery
0
 
LVL 9

Accepted Solution

by:
Venci75 earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
no - only the methods in the RemoteSql class
0
 

Author Comment

by:chencc77
Comment Utility
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
Comment Utility
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
Comment Utility
ok, I din see any connection busy in the program already. I hope it will work fine. Thanks again. ^-^
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now