Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes How to put Row level lock in java while inserting a row in MSSQL table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to put Row level lock in java while inserting a row in MSSQL table" Watch "How to put Row level lock in java while inserting a row in MSSQL table" New topic
Author

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

Lakshmi Ramachandran
Greenhorn

Joined: Jun 26, 2001
Posts: 26
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
DAYANAND BURAMSHETTY
Ranch Hand

Joined: Aug 06, 2001
Posts: 34
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.

Dil se....,<BR>Dayanand<BR>0065-8839071(off)<BR>0065-7547034(Res0
DAYANAND BURAMSHETTY
Ranch Hand

Joined: Aug 06, 2001
Posts: 34
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.

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to put Row level lock in java while inserting a row in MSSQL table
 
Similar Threads
problem in button ActionListener.
Any one have idea regarding this query??
problem in where clause while fetching records in jtable
particular SQLCODE bypass in batch mode
database design help needed