#217 SQL statement ignores isolation level

JDBC
closed
Kim Button
None
9
2006-05-22
2005-09-20
Guruman
No

When I select a row that has uncommited updates by
using isolation level 'read commited' I get the
uncommited result instead of the previous commited one.

Test environment:
- jt400: 4.5, 4.7, 4.8
- OS/400: V5R2, V5R3

Example:
import java.sql.*;

public class TestJDBCTransaction {

public static void main(String args[]) {
try {

Class.forName("com.ibm.as400.access.AS400JDBCDriver");
} catch (ClassNotFoundException e) {
System.out.println("Cannot find AS/400
driver!");
return;
}
create();
update();

System.exit(0);
}

private static void create() {
Connection con;
try {
con = getConnection();
con.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
con.createStatement().execute("DROP TABLE
TEST_TRANS");
} catch (SQLException e) {
}
try {
con.createStatement().execute(
"CREATE TABLE TEST_TRANS ("
+ "oid VARCHAR (16) NOT NULL, name
VARCHAR(35))");
con.createStatement().execute(
"ALTER TABLE TEST_TRANS "
+ "ADD CONSTRAINT TEST_TRANS_PK
PRIMARY KEY (oid)");
con.createStatement().execute(
"INSERT INTO TEST_TRANS
VALUES('_TEST_TEST_TEST_', null)");
} catch (SQLException e) {
System.out.println("Error executing SQL
statement:");
e.printStackTrace();
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

private static void update() {
Connection con;
try {
con = getConnection();
con.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
con
.createStatement()
.execute(
"UPDATE TEST_TRANS SET name =
'LOOSER' WHERE oid = '_TEST_TEST_TEST_'");
} catch (SQLException e) {
System.out.println("Error executing SQL
statement:");
e.printStackTrace();
}
read();
try {
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
read();
}

private static void read() {
Connection con;
try {
con = getConnection();
con.setAutoCommit(true);
con.setReadOnly(true);
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
PreparedStatement ps = con
.prepareStatement("SELECT oid, name
FROM TEST_TRANS "
+ "WHERE oid = '_TEST_TEST_TEST_'");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getRow() + ", " +
rs.getString(1) + ", "
+ rs.getString(2));
}
ps.close();

} catch (SQLException e) {
System.out.println("Error executing SQL
statement:");
e.printStackTrace();
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

private static Connection getConnection() throws
SQLException {
Connection con = DriverManager.getConnection(
"jdbc:as400://yourmachine/yourlib", "user",
"password");
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
return con;
}

}

The output on sysout is:
1, _TEST_TEST_TEST_, LOOSER
1, _TEST_TEST_TEST_, LOOSER

The expected output on sysout is:
1, _TEST_TEST_TEST_, null
1, _TEST_TEST_TEST_, LOOSER

Discussion

1 2 > >> (Page 1 of 2)
  • Guruman
    Guruman
    2005-09-20

    • priority: 5 --> 9
     
  • Jeff Lee
    Jeff Lee
    2005-09-20

    • assigned_to: nobody --> jlee-oss
     
  • Jeff Lee
    Jeff Lee
    2005-09-21

    Logged In: YES
    user_id=1221895

    You might be misunderstanding how isolation levels work. I
    believe you should be able to read your own (uncommitted)
    changes, which is what you are doing. Isolation level
    basically defines what other connections can or cannot see.
    There's a good explanation of isolation levels in the "JDBC
    API Tutorial and Reference", section 2.11.3.

     
  • Guruman
    Guruman
    2005-09-22

    Logged In: YES
    user_id=1107193

    In the code I use a different connection for the select. The
    method #getConnection() always creates a new one. Please
    look at the method #update(). It creates a new connection by
    calling #getConnection() and executes an update. Before
    commit it executes the method #read() that also creates a
    new connection and executes a select.

     
  • Jeff Lee
    Jeff Lee
    2005-09-28

    Logged In: YES
    user_id=1221895

    Please take a toolbox datastream and jdbc trace. (Set Java
    system property
    com.ibm.as400.access.Trace.category=DATASTREAM,JDBC)
    That way we can verify what is being set and what we send to
    the server. If JTOpen is doing everything correctly, than
    it is a server issue.

     
  • Guruman
    Guruman
    2005-09-30

    Logged In: YES
    user_id=1107193

    The attached file 'jt400jdbc.log' is the by the example
    created log.
    properties:
    - com.ibm.as400.access.Trace.category=DATASTREAM,JDBC
    - com.ibm.as400.access.Trace.file=jt400jdbc.log

     
  • Guruman
    Guruman
    2005-09-30

    JDBC log file

     
    Attachments
  • Jeff Lee
    Jeff Lee
    2005-09-30

    Logged In: YES
    user_id=1221895

    This might be a problem on the iSeries side. I'll forward
    this on to my DB2 contacts. In the meantime, you should
    verify that your system is current on database PTFs.

     
  • Jeff Lee
    Jeff Lee
    2006-02-17

    Logged In: YES
    user_id=1221895

    Reassigned this bug report to pauldevman.

     
  • Jeff Lee
    Jeff Lee
    2006-02-17

    • assigned_to: jlee-oss --> pauldevman
     
1 2 > >> (Page 1 of 2)