java.sql.Connection Isolation Level


I'm writing little spike code snippet that is not it giving me the the output I'm expecting.
I hv a table that is generally rows of counters. Other tables use these rows to generate what should be unique ID's.
When I execute the code snippet following what I excepted is that the 1st 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 generally completes before the 1st one due to it been put to sleep for 1s thus they both read the similar value and write the similar value so it is only increment once and not two times as I excepted.
Is there anything wrong with my code snippet or is my understanding of the Isolation level's incorrect?
I hv removed the boiler plate code snippet. Standard sql.Connection using a MySQL DB.
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 execute() {
DatabaseExample databaseExample = new DatabaseExample();
databaseExample.incrementValue();
}
};
new Thread(runnable).start();
databaseExample.incrementValue();
}




Get Source Code

  Views 93   Posted on : Saturday, May 18, 2013 4:31:52 AM
Related Tags:




Replies on "java.sql.Connection Isolation Level" :

Nobody has replied on this post , be first to post answer.


Post Your Answer/Comment:

Please Login first to post Reply !


Note: Please Put Code into <code></code> block.




Also See: