Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

update problem in production

 
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
 
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • 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
 
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.
 
There were millions of the little blood suckers. But thanks to this tiny ad, I wasn't bitten once.
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic