Java.sql.Connection isolation level
I am writing spike code that does not give me the expected results.
I have a table that is basically counter rows. Other tables use these strings to generate unique identifiers. When I run the code below, I rule out that the first thread that reaches the select statement will acquire a lock on that row or table, stopping all reads or writes by the unique id value. However, the second thread always ends before the first one, due to the fact that it was hibernated for 1s, so they both read the same value and write the same value, so it only increments once and not twice as I ruled out.
Is there something wrong with my code, or is my understanding of the isolation level wrong?
I have deleted the boiler plate code. Standard sql.Connection using 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 the 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();
}
source to share
Even at the SERIALIZABLE isolation level, multiple samples can be taken in parallel. If you want to put a lock on a row in a select clause use select ... for update
.
Literature:
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 locking, the rows validated by the query are write locked until the end of the current transaction. Using LOCK IN SHARE MODE establishes a shared lock that allows other transactions to read checked rows, but not update or delete them.
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_serializable :
SERIALIZABLE
This level is similar to REPEATABLE READ, but InnoDB implicitly converts all simple SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.
source to share