Two / more Connection Problem

[Franco Weichel]

When is use more than one connection within JDBC, changes i made are not visible in other connections.

here is my sample code:

Statement stmt = null;
Statement stmt2 = null;
ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

Connection conn1 = DriverManager.getConnection("jdbc:mysql://localhost/shema?user=user&password=secret");
conn1.setAutoCommit(false);

// create a test table and insert data 'one' for ID = 1
stmt = conn1.createStatement();
stmt.execute("create table TMPTEST (ID INT not null, DATA VARCHAR(10) null) ENGINE=InnoDB DEFAULT CHARSET=latin1");
conn1.commit();
stmt.executeUpdate("insert into TMPTEST (ID, DATA) values (1,'one')");
conn1.commit();

// query test table with second connection
Connection conn2 = DriverManager.getConnection("jdbc:mysql://localhost/shema?user=user&password=secret");
conn2.setAutoCommit(false);
stmt2 = conn2.createStatement();
rs = stmt2.executeQuery("select DATA from TMPTEST where ID = 1");

rs.next();
System.out.println("Expected 'one', is " + rs.getString(1));
rs.close();

// change DATA to 'two' in connection 1
stmt.executeUpdate("update TMPTEST set data = 'two' where ID = 1");
conn1.commit();

// again query test table with second connection
rs = stmt2.executeQuery("select DATA from TMPTEST where ID = 1");

rs.next();
System.out.println("Expected 'two', is " + rs.getString(1)); // <<<-- Here is the problem
rs.close();

// just to be sure table is changed
rs = stmt.executeQuery("select DATA from TMPTEST where ID = 1");

rs.next();
System.out.println("Expected 'two', is " + rs.getString(1) + " (from connection 1)");
rs.close();


stmt.close();
stmt2.close();

conn1.close();
conn2.close();

The output i got:

Expected 'one', is one
Expected 'two', is one <<<-- Here is the problem
Expected 'two', is two (from connection 1)


I use Version 4.1.11 and mysql-connector-java-3.1.8-bin.jar, all with default settings.

Any ideas?

Franco