• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
DAYANAND BURAMSHETTY
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.


 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Squanch that. And squanch this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic