Hi,
My program is updating one record in a table, and updated its related tables. This may takes much time(around 30 minutes).
I am not using AutoCommit.
If my program quits suddenly during execution (power cut, or process killed), the record it is trying to update is locked, and can't be updated by other applications.
So, is there any way to avoid such a problem?
Thanks in advance!
Use connection pooling to speed up you process by several orders of magnitude. Use autoCommit() with commit() and rollback() so you can roll back all changes to all tables if something goes wrong (you must do this, else you damage data in the database and cant recover from it).
As for taking 30 minutes, sounds like you used up all the connections in your get-a-connection experiments and are the database is holding onto those connections even if your program is killed. The database has a limited number of connections (1000 or so?) that kills the database if they are all used up. You need to get your database admin to free up those connections on his side (your killing his database). If its your database, restarting the database my clear the connections. Next, change your program to not only use connection pooling, but to propery close connections.
Your locking should not be a problem because any other programs accessing the database will lock a record for a very short time before
freeing it (because they are accessing the database correctly, as shown below, right?) . You can read up on optomistic and pestimistic locking if you want.
Here is an example of closing a connection properly (all are local variables except dataSource, therefore thread safe). I suggest your group's JDBC expert (that will soon be you) read up JDBC and its use.
public ArrayList myFunction(){
Connection conn=null;
PreparedStatement pstmt1= null;
ResultSet resultSet=null
ArrayList list1;
try{
list1=new ArrayList();
conn= dataSource.getConnection();
conn.setAutoCommit(false);
pstmt1= conn.prepareStatement();
resultSet= pstmt1.executeUpdate();
while(resultSet.next()){
arrayList.add(resultSet.getString("lastName");
}
conn.commit();
return arrayList;
} catch (SqlException e){
if(conn!=null)
conn.rollback();
e.printStackTrace();
} finally {
if(conn!=null)
conn.setAutoCommit(true);//must ensure this is done first because
//following lines might throw an error
if(rsultSet!=null)
resultSet.close();
if(pstmt1!=null)
pstmt1.close();
if(conn!=null)
conn.close();
}
}
}
Hi, George,
Thanks a lot for your suggestion.
Actually I am doing some refreshment in a jboss scheduler, this is done once a day. Refreshment means: suppose a country name changes, i am updating all the addresses which is in this country.
There are a lot of records in the address table, this is why it takes so much time.
We have to commit our changes intermittently (commit for every 50 countries). This is what I am currently doing
1. get a connection from the connection pool (Using only this connection for the entire process)
2. setAutoCommit(false)
3. update the country and related address tables
4. commit if records reach 50.
5. still commit if there are exceptions, because this is required by our business logic.
6. use the connection to refresh tables other than country table.
Is this fine?
