Friday, January 23, 2009

Oracle doesnt support dirty read

more details at


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleStatement;

public class ConnectionClass {
public ConnectionClass() {
}
public Connection getConnection() throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@hyc65003fwks.idc.oracle.com:1521:nn11g",
"scott","tiger");
con.setAutoCommit(false);
System.out.println("isolation lvl "+con.getTransactionIsolation());
con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
return con;
}

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
ConnectionClass obj = new ConnectionClass();
Connection con1 = obj.getConnection();
Connection con2 = obj.getConnection();
obj.doUpdate(con1);

obj.read(con2);
con1.rollback();
con1.close();
System.out.println("again reading.......");
obj.read(con2);
con2.close();
}
int i=0;
public void doUpdate(Connection con) throws SQLException {
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

i++;
String sql = "insert into delme values('aa"+i+"')";
System.out.println("sql >>>"+sql);
System.out.println(stmt.executeUpdate(sql));
stmt.close();
}
public void read(Connection con) throws SQLException {
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql = "select * from delme";
System.out.println("sql >>>"+sql);
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
stmt.close();

}


}
/* Exception in thread "main" java.sql.SQLException: READ_COMMITTED and SERIALIZABLE are the only valid transaction levels
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:3988)
at jdbcproj.ConnectionClass.getConnection(ConnectionClass.java:22)
at jdbcproj.ConnectionClass.main(ConnectionClass.java:29)
Process exited with exit code 1. */


No comments: