update problem in production

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);

Any One please help me

Satya,
Look into database transactions.

You have the "lost update" problem where one update is overwriting another because multiple SQL statements are done.

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

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.

Thank you Derby. The link is of more help.