• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

update problem in production

 
satya kiran
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

The following code is a sample application, which accesses a shared SQL database. This code seemed ok in test but causes some unrelated updates of salary to be occasionally lost in production. Why could this happen? What options are there to resolve this problem? Your suggestions will help me alot.

Thanks in advance.



import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class Operation {
private Connection c;

private void mainLogic(long empId) throws SQLException {
BigDecimal salary = selectSalary(empId);
salary = indexSalary(salary);
updateSalary(empId, salary);
}

public void execute(Connection aConn, long empId)
throws SQLException {
c = aConn;
c.setAutoCommit(false);
try {
mainLogic(empId);
c.commit();
} catch (SQLException e) {
c.rollback();
throw e;
}
}

private BigDecimal selectSalary(long empId) throws SQLException {
PreparedStatement ps = c.prepareStatement(
"SELECT salary FROM emp WHERE emp_id = ?");
ResultSet rs = null;
try {
ps.setLong(1, empId);
rs = ps.executeQuery();
rs.next();
return rs.getBigDecimal("salary");
} finally {
if (rs != null) {
rs.close();
}
ps.close();
}
}

private BigDecimal indexSalary(BigDecimal salary) {
return salary.multiply(new BigDecimal("1.1"));
}

private void updateSalary(long empId, BigDecimal salary)
throws SQLException {
PreparedStatement ps = c.prepareStatement(
"UPDATE emp SET salary = ? WHERE emp_id = ?");
try {
ps.setBigDecimal(1, salary);
ps.setLong(2, empId);
ps.executeUpdate();
} finally {
ps.close();
}
}

public static void main(String args[]) throws Exception {
Class.forName(args[0]);
Connection c = DriverManager.getConnection(args[1], args[2], args[3]);

Operation op = new Operation();
op.execute(c, Long.parseLong(args[4]));
}
}

Sample DDL is for emp is:

CREATE TABLE emp ( emp_id NUMERIC PRIMARY KEY,
salary NUMERIC NOT NULL);
 
satya kiran
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any One please help me
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Satya,
Look into database transactions.

You have the "lost update" problem where one update is overwriting another because multiple SQL statements are done.
 
satya kiran
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Jeanne.

So, will it work if i change the call to the method mainLogic in
synchronize block.

i.e., synchronize {
mainLogic()
}

(or) what other ways i can eliminate this problem.

Please clarify,

Thanks in advance for your help

Regards,
Satya

Many thanks for you
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by satya kiran:
Thank you Jeanne.

So, will it work if i change the call to the method mainLogic in
synchronize block.



No.

Jeanne's guessing that your problem is the classic "lost update" problem.
http://db.grussell.org/section012.html#_Toc67114500
If this is you problem, this can not be fixed at the pure Java level. It can only be fixed by either having all clients use different transactionality levels (such as serializable), or by employing locking on the database.
 
satya kiran
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Derby. The link is of more help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic