Crazy Idea


Jdbc : Why doesn’t Oracle’s driver support READ_UNCOMMITTED ?
September 7, 2007, 6:56 pm
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,

About these ads

15 Comments so far
Leave a comment

Because transaction isolation levels READ_UNCOMMITTED and REPEATABLE_READ don’t exist in Oracle databases by design.

Comment by Morgan

Hi 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,
~A

Comment by anjanb

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

For your convenience, Tom’s answer can be found on his site.

Comment by Dr. Java

My 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

answering 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

Hi friends, how is everything, and what you wish for
to say about this piece of writing, in my view its in
fact amazing in support of me.

Comment by free fps games

I just like the helpful information you provide on your articles.
I’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!

Comment by youtube.com

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/

Hello just wanted to give you a quick heads up.

The words in your content seem to be running off the screen in Internet explorer.
I’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

Comment by crear facebook

magnificent points altogether, you simply won
a new reader. What might you suggest in regards to
your put up that you just made some days in the past?
Any certain?

Comment by crear facebook

What a stuff of un-ambiguity and preserveness of precious knowledge regarding
unexpected feelings.

Comment by Trulygraceful.blogspot.Co.uk

I would like to thank you for the efforts you have put in writing this website.
I 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 ;)

Comment by cli.gs

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
have 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.

Comment by Rocio Tanner

Excellent blog here! Also your website loads up fast!
What 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

Comment by Gonzalo




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



Follow

Get every new post delivered to your Inbox.

%d bloggers like this: