Filed under: Uncategorized
hi lazy Web,
I tried to change the Transaction Isolation level to see what my unit test(spring/hibernate/dbUnit) was doing.
Class.forName(”oracle.jdbc.driver.OracleDriver”);
Connection conn = java.sql.DriverManager.getConnection(url, user, password);
if (null != conn) {
System.out.printf(”%s%n”, conn.getTransactionIsolation());
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
}
and got the following exception
2
Exception in thread “main” java.sql.SQLException: READ_COMMITTED and SERIALIZABLE are the only valid transaction levels
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:20 
at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:167
at JdbcTest.main(JdbcTest.java:17)
So, why doesn’t Oracle JDBC driver let me do dirty reads ?
Does anyone out there know of a tool (java or otherwise) that can let me do this ? I have TOAD : does TOAD support a way to do this ? How about ruby/Python tools ? I’m on windows XP. So, it would help me if you could refer me to windows specific OR cross-platform tools.
Thank you for any pointers,
4 Comments so far
Leave a comment
Leave a comment
Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
Because transaction isolation levels READ_UNCOMMITTED and REPEATABLE_READ don’t exist in Oracle databases by design.
Comment by Morgan September 7, 2007 @ 9:34 pmHi Morgan,
Thank you.
Guess it’s time to refresh my Oracle fundas by reading the WROX PRESS “EXPERT ONE-ON-ONE ORACLE”. I’m sure Tom would give me a hint as to why they do it.
Do, do other DBs(like MS-SQL, MySQL, DB2) allow Dirty Reads ? How about Java DB, HSQL, etc ?
Thanks again,
BR,
Comment by anjanb September 8, 2007 @ 12:48 am~A
Databases that use Multiversion concurrency control (oracle, postgresql, mysql&innodb, firebird etc) don’t need dirty reads. A dirty read is useful if you want to do a non blocking read, but Multiversion Concurrency Control (MVCC) database have a different solution. Instead of reading uncommitted data, a previous committed version of a record is used (hence the name multiversion). This approach is much saver because you can’t read uncommitted data (so data that never made it into the db) or data that is not valid (data could be written before its constraints are checked).
That is why a READ_UNCOMMITTED isolation level is ugraded to READ_COMMITTED.
A similar explanation can be used for REPEATABLE_READS. The SERIALIZED isolation level also prevents unrepeatable reads from happening and the serialized isolation level is quite easily realized in oracle (also based on the multiversion mechanism and optimistic locking at the end of serialized transaction commit). That is why REPEATABLE_READ is automatically upgraded to SERIALIZED.
Comment by peter veentjer September 8, 2007 @ 3:19 pmFor your convenience, Tom’s answer can be found on his site.
Comment by Dr. Java October 3, 2007 @ 11:33 am