Franco Weichel wrote:
> 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://localhos
> t/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://localhos
> t/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
[snip] - your problem _is_ here
Franco,
By default, InnoDB runs in isolation level REPEATABLE_READ, which means that conn2's "view" of the data will be stable within the span of a given transaction. This in effect means that you won't see any changes from conn1 on conn2 that occurred before conn2 committed. You either need to commit() on conn2 to see the change, or reduce the isolation level to READ_COMMITTED by calling conn2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED).
Regards,
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html