java.sql.Connection Isolation Level

Question

I am writing some spike code that isn't giving me the the results I am expecting.

I have a table that is basically rows of counters. Other tables use these rows to generate what should be unique ID's. When I run the code below what I excepted is that the first thread to reach the the select statement would acquire a lock on that row, or table, stopping all read or writes on the unique id value. However the second thread always completes before the first one, due to it been put to sleep for 1s, thus they both read the same value and write the same value, so it's only increment once and not twice as I excepted.

Is there anything wrong with my code, or is my understanding of the Isolation level's incorrect?

I have removed the boiler plate code. Standard sql.Connection using a MySQL Database.

private void incrementValue() {

        connection
                .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        statement = connection.createStatement();

        System.out.println(Thread.currentThread().getName()
                + " doing select");
        resultSet = statement.executeQuery("select * from counter");
        System.out.println(Thread.currentThread().getName()
                + "after select");
        if (counter++ == 0) {
            Thread.sleep(1000);
        }
        String incrementedValue = getIncrementedValue(resultSet);

        statement.executeUpdate("update counter set counter='"
                + incrementedValue + "'");


}

private String getIncrementedValue(ResultSet resultSet) throws SQLException {
    String value = "";
    if (resultSet.next()) {
        System.out.println(Thread.currentThread().getName() + "Value was "
                + resultSet.getString(1));

        value = (new Integer(resultSet.getString(1)) + 1) + "";

    }

    return value;

}

This is called from main

public static void main(String[] args) {
    DatabaseExample databaseExample = new DatabaseExample();

    Runnable runnable = new Runnable() {

        @Override
        public void run() {
            DatabaseExample databaseExample = new DatabaseExample();
            databaseExample.incrementValue();
        }
    };
    new Thread(runnable).start();

    databaseExample.incrementValue();
}

In this case, the lock must be made at level application. Just synchronize your method using the keyword `synchronized`. Note that this will impact on the application performance. — Luiggi Mendoza, Feb 09 '13 at 15:00
You may want to check into having your ids generated using a sequence or identity, or using a Hilo scheme. Any of which sound better than what you describe. — Nathan Hughes, Sep 06 '18 at 23:59

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Even in the SERIALIZABLE isolation level, multiple selects can be made in parallel. If you want to put a lock on the row on the select clause, use select ... for update.

References:

http://dev.mysql.com/doc/refman/5.1/en/select.html :

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them.

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

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.

JB Nizet: Thanks for the reply. I was hoping for a non database specific solution as this will need to run on databases from different vendors but there doesn't seem to be one. FOR UPDATE seems to work on MySQL and Oracle and on SQL Server using WITH (UPDLOCK) while catching the deadlock exception and re-running the code. — Medu, Feb 10 '13 at 21:57

java.sql.Connection Isolation Level

1 Answers1