How to put Row level lock in java while inserting a row in MSSQL table

Hi folks,
I am writing a java application where data is getting inserted in a table, say, table1 in database, say, db1. The back-end is MS SQL server 7.0. I am using JDBC-ODBC bridge.
Now, the thing is when I a have one client then the application is running properly. But when there are multiple clients, deadlock occurs on the table - table1 in db1. I feel that by default the insert statement puts table level lock (optimistic lock) on table1. I would like to put exclusive lock. Is there any way to put Row level lock in java while inserting a row in MSSQL table?
This is very urgent!
I would be greatful if someone can help me out in this regard.
Please respond ASAP.

Thanks & Regards
Lakshmi

How to put Row level lock in java while inserting a row in MSSQL table ?
Hi Lakshmi Ramachandran,
If U r using EJB no need worry about this things but
everytime U have to close connection(ResultSet etc..)
otherwise it gives the exception.
Example:
public long insert(UserTB myrecord) throws CreateException {
Connection dbConnection=null;
Statement st=null;
ResultSet rs=null;
PreparedStatement ps=null;
String strQry=null;
try{

dbConnection = getConnection();
st=dbConnection.createStatement();
rs=st.executeQuery("SELECT USERID FROM SEA_USER WHERE USERID='"+pk+"'");
if(!rs.next()){
strQry = " INSERT INTO USER (USERID, ..... ) " +
" VALUES (?, ........ )";
ps = dbConnection.prepareStatement(strQry);
ps.setString(1, myrecord.getUserID());
.......
}//if(!rs.next()
else{
throw new SQLException("User ID already exists...");
}
return myrecord.getUserID() ;
}catch(SQLException se){
throw new CreateException(se.getMessage());
}finally{
//This is one very importent
try {
if (rs!=null) rs.close();
if( st != null) st.close();
if (ps != null ) ps.close();
if (dbConnection != null ) dbConnection.close();
}catch(Exception e) {
throw new CreateException("EXCEPTION WHEN CLOSE RESOURCE IN INSERT ");
}
}
}//EOF INSERT
If U r not using EJB U have to create connectionpool
using Vector,Threds...etc., If U need I will send
code.

Originally posted by DAYANAND BURAMSHETTY:
How to put Row level lock in java while inserting a row in MSSQL table ?
Hi Lakshmi Ramachandran,
If U r using EJB no need worry about this things but
everytime U have to close connection(ResultSet etc..)
otherwise it gives the exception.
Example:
public long insert(UserTB myrecord) throws CreateException {
Connection dbConnection=null;
Statement st=null;
ResultSet rs=null;
PreparedStatement ps=null;
String strQry=null;
try{

dbConnection = getConnection();
st=dbConnection.createStatement();
rs=st.executeQuery("SELECT USERID FROM SEA_USER WHERE USERID='"+pk+"'");
if(!rs.next()){
strQry = " INSERT INTO USER (USERID, ..... ) " +
" VALUES (?, ........ )";
ps = dbConnection.prepareStatement(strQry);
ps.setString(1, myrecord.getUserID());
.......
}//if(!rs.next()
else{
throw new SQLException("User ID already exists...");
}
return myrecord.getUserID() ;
}catch(SQLException se){
throw new CreateException(se.getMessage());
}finally{
//This is very importent
try {
if (rs!=null) rs.close();
if( st != null) st.close();
if (ps != null ) ps.close();
if (dbConnection != null ) dbConnection.close();
}catch(Exception e) {
throw new CreateException("EXCEPTION WHEN CLOSE RESOURCE IN INSERT ");
}
}
}//EOF INSERT
If U r not using EJB U have to create connectionpool
using Vector,Threds...etc., If U need I will send
code.

You can set these parameters using Connection.setTransactionIsolation(XXXX);
where XXXX is equal to:
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE
You will have to see which level is optimum for your software. The more stringent you get, the lower the number of cuncurrent users and the slower the process is for statements/resultsets.
Jamie