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:208)
at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:1678)
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,
15 Comments so far
Leave a comment
Crazy Idea 
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 amMy understanding is that Oracle just cannot do the READ_UNCOMMITTED because it uses versioned snapshots. Once you started reading, you read the same data from the snapshot which is detached from the “current” state of the table. This approach to concurrency makes READ_UNCOMMITTED simply impossible.
Comment by Slava Imeshev August 10, 2008 @ 8:19 amanswering the tool part of the question myself.
datastorm is a tool : http://datastorm.sourceforge.net/motivation.html
that will launch a SWT app with the same connection used for the unit test.
this tool lets the unit tester to go into the GUI and then run queries for all you want. That will make unit testing a lot more easier for those difficult cases.
It might NOT even be a bad idea to have the setup() method launch the datastorm tool by default — just in case it is needed. Anyhow, most of the time, connection is obtained once per test case.
Comment by anjan bacchu October 16, 2008 @ 8:47 amHi friends, how is everything, and what you wish for
Comment by free fps games April 17, 2013 @ 2:04 pmto say about this piece of writing, in my view its in
fact amazing in support of me.
I just like the helpful information you provide on your articles.
Comment by youtube.com April 28, 2013 @ 3:48 amI’ll bookmark your weblog and check again here regularly. I am rather sure I will be informed many new stuff right here! Best of luck for the following!
Hola! I’ve been following your weblog for a long time now and finally got the bravery to go ahead and give you a shout out from Austin Tx! Just wanted to mention keep up the good job!
Comment by http://xn--vk1b6fz24ctgizza.com/ May 7, 2013 @ 7:04 pmHello just wanted to give you a quick heads up.
The words in your content seem to be running off the screen in Internet explorer.
Comment by crear facebook May 8, 2013 @ 6:37 amI’m not sure if this is a format issue or something to do with web browser compatibility but I thought I’d
post to let you know. The design look great though! Hope you get the issue resolved soon.
Thanks
magnificent points altogether, you simply won
Comment by crear facebook May 29, 2013 @ 9:35 ama new reader. What might you suggest in regards to
your put up that you just made some days in the past?
Any certain?
What a stuff of un-ambiguity and preserveness of precious knowledge regarding
Comment by Trulygraceful.blogspot.Co.uk May 31, 2013 @ 12:27 pmunexpected feelings.
I would like to thank you for the efforts you have put in writing this website.
Comment by cli.gs June 2, 2013 @ 2:39 amI am hoping to view the same high-grade blog posts from you in the
future as well. In truth, your creative writing
abilities has inspired me to get my very own site now 😉
It was so shockingly open-handed of you to offer extensively just what a number of us might have made
available for an e-book to make some money on their
own, mostly now that you could have done it in the event you considered necessary.
Those basics additionally acted like a easy way to understand that some people
Comment by Rocio Tanner January 11, 2014 @ 7:13 pmhave the identical passion just as my own to grasp lots more related to this condition.
I’m certain there are many more pleasant periods in the future for people who read your blog.
Excellent blog here! Also your website loads up fast!
Comment by Gonzalo August 26, 2014 @ 3:54 amWhat web host are you using? Can I get your affiliate link to your
host? I wish my site loaded up as quickly as yours lol