Re: flawed transaction isolation level


carlo wrote:
Hello all.

I'm using mysql v5 and mysql connector Connector/J v3.1 the below
example doesn't work!!

2 threads are reading and updating a value from a column in the
database. The transactions should ensure a correct result, however this
is not the case. E.g. both threads reads value 10 and writes back a
9... what am i doing wrong here? Using v5 of the connector/J (which I
just used for testing.. I cant use it in my live app.) it worked with
isolation level serializable, but not any of the other isolation
levels!?!?


You would need to read up on what the isolation levels guarantee.
It seems to me that everything is fine so far. Serializable guarantees
that the final results will be as if the two transactions had been done
individually in a serial fashion. Lesser isolation levels don't
guarantee
that. If you want your read data to be locked until the end of the tx,
you serializable isolation. In fact, that's not even sufficient. Oracle
*never*
locks read data unless you specifiy "for update" in your SQL. This is
a bit more involved than your current (and a lot of people's)
understanding
and needs some looking it up to get it exactly right.
Joe Weinstein at BEA Systems

import java.sql.*;
import java.util.*;

public class AllInOne {
Connection con1, con2;

public AllInOne() throws SQLException, ClassNotFoundException {
Class.forName("org.gjt.mm.mysql.Driver");
con1 =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",
"root");
con2 =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",
"root");
}

/** create DB table and insert a product */
private void firstRun() throws SQLException {
Statement stmt = con1.createStatement();
stmt.executeUpdate("CREATE TABLE stock( id int, physical int,
primary key(id))");
stmt.executeUpdate("INSERT INTO stock VALUES (1, 10)");
stmt.close();
}


public static void main(String[] args) throws Exception {
Test t = new Test();
//t.firstRun(); // run only first time to create DB
User w1, w2;

t.con1.setAutoCommit(false); t.con2.setAutoCommit(false);

t.con1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

t.con2.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
w1 = new User(t.con1, 200, true);
w2 = new User(t.con2, 600, true);
w1.start(); w2.start();

}


public static class User extends Thread {
int delay;
Connection conn;
String sqlFetch, sqlUpdate;
boolean commit;

public User(Connection conn, int delay, boolean commit) {
this.delay = delay;
this.conn = conn;
this.sqlFetch = "SELECT physical FROM stock WHERE id=1";
this.sqlUpdate = "UPDATE stock SET physical=";
this.commit = commit;
}

public void run() {
Statement stmt = null;
try {
// before
int result = fetch();
System.out.println("stock is: "+result + " "
+this.toString());

// wait a bit
synchronized(this) { this.wait(delay); }

// after
update(result-1);

if(commit)
conn.commit();


System.out.println("end result: "+ fetch() + " " +
this);
}
catch(Exception e) {
e.printStackTrace();
}
finally {
if(stmt != null) {
try {
stmt.close();
}
catch(Exception e) {e.printStackTrace();}
}

if(conn != null) {
try {
conn.close();
}
catch (Exception e) {e.printStackTrace(); }
}
}
}


int fetch() throws Exception {
Statement stmt = null;
try {
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sqlFetch);
int result;
if(!rs.next())
throw new Exception("No result from query
'"+sqlFetch+"'");
result = rs.getInt(1);
return result;
}
finally {
if(stmt != null)
stmt.close();
}
}

void update(int value) throws SQLException {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sqlUpdate+value);
}
}
}

.