Implementing a counter JDBC concurrency issues

Question

I am trying to implement a counter in JDBC the method essentially increments a column value and returns the new column value, below is a snippet of my code i first perform a select .. for update to get the old value(also resulting in a row lock) then performing an update on that row. Am i right to assume that no other transaction can interfere with the value of the column val between the select .. for update then update.

        //get connection here
        con.setAutoCommit(false);
        PreparedStatement statement = con.prepareStatement("SELECT val FROM atable WHERE id=? FOR UPDATE");
        statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            oldVal = resultSet.getInt("val");
            statement = con.prepareStatement("UPDATE atable SET val=" + oldVal + "+1 WHERE id=?");
            statement.setInt(1, id);
            int result = statement.executeUpdate();
            if (result != 1) {
                throw new SQLException("error message");
            } else {
                newVal = oldVal + 1;//could do another select statement to get the new value

                statement.close();

            }
        } else {
                throw new SQLException("error message");
        }
        resultSet.close();
        con.commit();
        con.setAutoCommit(true);
       //close connection here

Thanks.

Can you describe why you are creating a counter? Generally you can accomplish everything you need with triggers or aggregations. — Nix, Jan 10 '13 at 14:32
If you need to obtain unique ids, you could try obtaining blocks of say 100 or 1000 ids reducing the JDBC overhead by that factor. — Peter Lawrey, Jan 10 '13 at 14:34

Aaron Kurtzhals · Answer 1 · 2013-01-10T15:02:10.233

I would do something like the below code instead. (I took out checks and exceptions for readabilty; feel free to add them back in.)

con.setAutoCommit(false);
PreparedStatement incrementVal = 
    con.prepareStatement("UPDATE atable SET val = val + 1 WHERE id=?");
incrementVal.setInt(1, id);
statement.executeUpdate();
PreparedStatement selectIncrementedVal = 
    con.prepareStatement("SELECT val FROM atable WHERE id=?");
selectIncrementedVal.setInt(1, id);
ResultSet resultSet = selectIncrementedVal.executeQuery();
if (resultSet.next()) {
    newVal = resultSet.getInt("val");
}
resultSet.close();
con.commit();
con.setAutoCommit(true);

See information about transaction isolation below.

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_serializable

My earlier implementation was actually like this, but i got the idea that perhaps between the update and select another transaction might update hence resulting this transaction selecting the wrong updated value? — JCS, Jan 10 '13 at 14:48

score 0 · Accepted Answer · edited Jan 10 '13 at 15:04

0

Am i right to assume that no other transaction can interfere with the value of the column val between the select .. for update then update

Yes you can.Otherwise it may lead to `inconsistency in database .

edited Jan 10 '13 at 15:04

Buhake Sindi

87,898
29
167
228

answered Jan 10 '13 at 14:44

joey rohan

3,505
5
33
70

score 0 · Answer 3 · edited May 23 '17 at 11:43

I was going to add something about the database's current isolation level playing a role, but in my uncertainty, I googled around, and found a few other interesting articles: SQLServer Question and Oracle-centric answer.

Ultimately, I'm not sure (sorry). I might suggest a stored procedure to provide the explicit atomic locking you want, and have it return the new value to your code, if needed. The stored procedure syntax may be database vendor specific, however.

(btw, you don't show how you acquire the Connection reference. If there are multiple threads using the same connection at the same time, they would be able to modify the "auto commit" attribute behind your back. Most likely you're using connection pooling, so only one thread has this reference at any given time.)

Also note that the database isolation level, if it turns out to be relevant, is set on a Connection level also, so if you're getting the connection out of a pool, the previous user may have left the connection in a different state than what you need. You were prudent to restore the setAutoCommit() attribute; you may need to do the same with the setTransactionIsolation() attribute.

I added comments to the code to indicate where i make and close the connection, the set autocommit is only for a connection so it should be fine as long as different threads are making seperate connections? — JCS, Jan 10 '13 at 15:01
yes, it sounds like you're good. Just don't share the connection by different threads at the same time. — dashrb, Jan 10 '13 at 16:03

score 0 · Answer 4 · answered Jan 10 '13 at 14:50

With Oracle, you could do the following:

CallableStatement cs = con.prepareCall(
  "UPDATE atable SET val = val + 1 WHERE id=? RETURNING val INTO ?");
cs.setInt(1, id); 
cs.registerOutParameter(2, Types.NUMERIC); 
cs.execute();
int newId = cs.getInt(2);

Implementing a counter JDBC concurrency issues

4 Answers4