tricky jdbc issue

Discussion in 'Java' started by Qaran, May 11, 2005.

  1. Qaran

    Qaran Guest

    1. 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);
    Qaran, May 11, 2005
    #1
    1. Advertising

  2. Qaran wrote:
    >
    > 1. 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);


    Your code seems ok, assuming this is what you are actually running in
    production. What transaction isolation level is being used with this
    connection?

    --
    Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    ==============================================================
    * The Ultimate DBMS is here!
    * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
    Lee Fesperman, May 11, 2005
    #2
    1. Advertising

  3. In article <>,
    "Qaran" <> wrote:

    > 1. 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);
    > }


    Ha ha. You're going to fail your class if you're cheating on homework
    this easy. You're so lazy that you didn't even edit out the homework
    hints or the question number.
    Kevin McMurtrie, May 11, 2005
    #3
  4. IMHO the problem is your SELECT statement (method selectSalary):

    "SELECT salary FROM emp WHERE emp_id = ?"

    I would use a SELECT ... FOR UPDATE in your situation.

    Best,
    Manfred
    Manfred Rosenboom, May 11, 2005
    #4
  5. Qaran

    Ross Bamford Guest

    On Tue, 2005-05-10 at 23:03 -0700, Qaran wrote:
    > 1. 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?


    How many marks are there for this? ;)

    Another hint: Focus on coreLogic and the fact that it's not forced to
    happen in immediate sequence from the database point of view.

    Or, what if I select a salary, and while I'm doing my indexSalary thing
    someone else selects one. I write my update (while they index theirs),
    and go off. They (finishing their index) write theirs. My update is
    goneski.

    What would you do to fix it? This is where the marks are my friend :)

    Ross

    --
    [Ross A. Bamford] [ross AT the.website.domain]
    Roscopeco Open Tech ++ Open Source + Java + Apache + CMF
    http://www.roscopec0.f9.co.uk/ + in
    Ross Bamford, May 11, 2005
    #5
  6. Qaran

    shakah Guest

    Ross Bamford wrote:
    > On Tue, 2005-05-10 at 23:03 -0700, Qaran wrote:
    > > 1. 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?

    >
    > How many marks are there for this? ;)
    >
    > Another hint: Focus on coreLogic and the fact that it's not forced to
    > happen in immediate sequence from the database point of view.
    >
    > Or, what if I select a salary, and while I'm doing my indexSalary

    thing
    > someone else selects one. I write my update (while they index

    theirs),
    > and go off. They (finishing their index) write theirs. My update is
    > goneski.
    >
    > What would you do to fix it? This is where the marks are my friend :)
    >
    > Ross
    >


    Why is your update "goneski"? Wouldn't the same value just get written
    twice to the DB?
    shakah, May 11, 2005
    #6
  7. Qaran

    Ross Bamford Guest

    On Wed, 2005-05-11 at 04:33 -0700, shakah wrote:
    > Ross Bamford wrote:
    > > On Tue, 2005-05-10 at 23:03 -0700, Qaran wrote:
    > > > 1. 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?

    > >
    > > How many marks are there for this? ;)
    > >
    > > Another hint: Focus on coreLogic and the fact that it's not forced to
    > > happen in immediate sequence from the database point of view.
    > >
    > > Or, what if I select a salary, and while I'm doing my indexSalary

    > thing
    > > someone else selects one. I write my update (while they index

    > theirs),
    > > and go off. They (finishing their index) write theirs. My update is
    > > goneski.
    > >
    > > What would you do to fix it? This is where the marks are my friend :)
    > >
    > > Ross
    > >

    >
    > Why is your update "goneski"? Wouldn't the same value just get written
    > twice to the DB?
    >

    Exactly. Two calls have been made, but both incremented from the same
    base value, so only one increment has been performed.

    e.g. imagine a simple case where we have two 'clients' (these could be
    individual computers, individual processes / threads on a single
    computer, or completely separate processes (such as the Java system, and
    an admin on the MySQL console). Both decide to update the value 'at the
    same time'. In this case they're on the same computer:

    Bear in mind that it doesn't really happen like that - the computer is
    sharing time between the two clients (and probably many other processes
    too). This is why the problem occurs - it's possible for a client to
    decide to update the value, fetch it, and then get interrupted. Another
    client decides to do the same thing, fetches the (same) value, and then
    it, too gets interrupted, and the first client wakes up. It then does
    the index (which involves multiplying the value), writes it, and gets
    interrupted. The second client, oblivious to any of this (or even the
    fact that it has been interrupted), wakes up writes it's value, the same
    value the original just wrote. Result: Two calls to increment, but only
    one increment (and one "lost" value):

    Client1.value = getValue(); /* 10 */
    Client1.value = indexValue(Client1.value); /* 11 */

    --- interrupted ---

    Client2.value = getValue(); /* 10 */
    Client2.value = indexValue(Client2.value); /* 11 */

    --- interrupted ---

    updateValue(Client1.value); /* writes '11' */

    --- interrupted ---

    updateValue(Client2.value); /* writes '11' */


    In the case where clients are on different computers the situation is
    different in detail (obviously no need for interruptions etc) but the
    essence of the issue is the same. There is no guarantee that the Server
    methods involved will fully execute before started again by another
    thread. They are not 'atomic' operations, because the JVM or host
    scheduler can break them up as required.

    This is a *very* simplified explanation, but you get the idea. It's much
    the same issues as thread synchronization, and there is indeed much
    overlap between synchronization and concurrency. You'll find a hundred
    thousand better explanations that this one with a simple google search.

    Cheers,
    Ross
    --
    [Ross A. Bamford] [ross AT the.website.domain]
    Roscopeco Open Tech ++ Open Source + Java + Apache + CMF
    http://www.roscopec0.f9.co.uk/ + in
    Ross Bamford, May 11, 2005
    #7
  8. Qaran

    steve Guest

    On Wed, 11 May 2005 14:03:38 +0800, Qaran wrote
    (in article <>):

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


    gez, apart from this being homework.

    the updates , should be select for update, thereby providing locking, that
    will prevent "shared" problems.

    the select salary recalculate & update salary are in 3 routines, you should
    never return a value from a database, from 1 routine , then update that value
    before putting it back into the database in another routine.

    Select & update in the same call, but ensure you lock the record you are
    updating.

    something like:
    Salary_inc=1.1
    " select for update set salary=salary*? where emp_id=?"
    ps. setbigdecimal(1Salary_inc);
    ps.setLong(2, empId);
    ps.executeUpdate();
    c.commit(); // this must be here to clear the lock

    } catch (SQLException e) {
    c.rollback(); // this must be here to clear any lock
    throw e;
    }

    but you would need to trap the specific error that the record is locked, by
    another user, and return a "sorry record busy " message.

    this is just to give you a general idea why the code is WRONG, but you would
    not do it this way in production.

    steve
    steve, May 13, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    747
    Keith Wansbrough
    Aug 16, 2004
  2. Betty
    Replies:
    1
    Views:
    9,861
    Juha Laiho
    May 21, 2005
  3. Jesus M. Salvo Jr.
    Replies:
    2
    Views:
    4,135
    robert
    Feb 11, 2006
  4. Bruce
    Replies:
    4
    Views:
    750
    Bjorn Abelli
    Mar 25, 2006
  5. Replies:
    9
    Views:
    531
    CBFalconer
    Apr 25, 2006
Loading...

Share This Page