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!?!?

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);
       }  
   }    
}