tricky jdbc question

The following code is a complete 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? Hint: Focus on coreLogic.

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 coreLogic(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 {
coreLogic(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);

Granger,
Do I get the credit points if I provide a correct answer (to your homework assignment)?

The answer is quite obvious. The question is framed in such a way that it practically includes the answer.

Your poor, old professor must be pulling his hair out -- trying his hardest to help you get passing grades on his assignments. And what reward does he get? His students still try to cheat. Really sad. I know it's a cop-out, but I guess I'm glad I won't be around when your generation takes over.

Yes, I'm grossly generalizing, and I'm assuming a lot about you and your situation -- so please, by all means, correct me if I'm wrong.

Good Luck,
Avi.

yes avi, homework problem indeed. here's what i think is the problem. in pure and simple terms - synchronization. my guess is that while say one person has read the value but before he can update that value, a second person updates the value on the database. now when the first person gets around to updating also, the second person's update is written over. again i'm not really sure about this since i don't understand the interaction from the database's point of view. however if this premise is right then i would probably put the corelogic code in a synchronized block or synchronize the entire method.

am i right ?

"crookshank granger"
I'm as big a Harry Potter fan as the next person, but we require proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks,
Jeanne
Forum Bartender

Granger,
I see you got some answers on the "comp.lang.java.databases" newsgroup, where you also posted your assignment question. Is that where you got the idea for your reply here? There's nothing I can add to the replies you got there.

Good Luck,
Avi.

Wow Jeanne and Avi, call me easily impressed but I think you guys are good, spooky good. Sherlock Holmes would be proud of you two, you guys didn�t miss a thing in �Chookshank�s� posts. I don�t mind that I couldn�t see that it was homework, I�m by no means as good at this stuff as most of the people on these boards. However, I love Harry Potter and I missed that one. I knew the name wasn�t real, but I couldn�t quite place where I�d seen �crookshank� before (Granger should have been a dead give away). You both have inspired me to work harder and to try to observe what�s in front of my face a bit better.