Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!

crookshank granger

Greenhorn
+ Follow
since May 10, 2005
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by crookshank granger

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