tricky jdbc issue

Q

Qaran

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

Lee Fesperman

Qaran said:
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);
Click to expand...

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

Kevin McMurtrie

Qaran said:
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);
}
Click to expand...

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.
 
M

Manfred Rosenboom

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
 
R

Ross Bamford

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?
Click to expand...

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
 
S

shakah

Ross said:
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
Click to expand...

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

Ross Bamford

Why is your update "goneski"? Wouldn't the same value just get written
twice to the DB?
Click to expand...
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
 
S

steve

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);
Click to expand...

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQLite issue 3
Connected SQLite to my java program but information are not submitted 2
Need help with JDBC code walk 3
Closed Statement issue 7
Can anybody plz help me outof this problem in mah code 0
jdbc ClassNotFoundException 3
plz help to find the mistake.....beginner 4
jdbc query 2

Members online

No members online now.
Total: 32 (members: 1, guests: 31)
Robots: 476

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,054
Latest member
TrimKetoBoost

Latest Threads

Top