This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes JDBC isolation level vs query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC isolation level vs query" Watch "JDBC isolation level vs query" New topic

JDBC isolation level vs query

Guru Java

Joined: Jul 26, 2005
Posts: 2
I'm using an EJB container (websphere) in which I use a JDBC datasource. The settings on the datasource let me define a JDBC transaction isolation level (serializable,repeatable read etc). I do understand the differences of these settings, but I do not understand the following:

Regardless of the isolation setting on the JDBC connection I can perform queries which include this information also. I'm using DB2 as database, so I can perform the following query:

"select * from where id=1 for update with rr"

This query results in a row lock. Its impossible for another transaction to delete or modify row with id=1. So even if my transaction isolation was set to 'read uncommited' this query will prevent any modification to the row read.

So my questions are as followed:

- Is it true that the JDBC transaction isolation option is the same as the options I can specify within my SQL queries ? So they do basicly the same thing.

- Can I look at the JDBC transaction isolation option to work as a default for *all* the queries made by that connection ? So even queries that do not specify anything related to isolation, they will still be executed with the level specified for the JDBC connection

- May queries perform more fine grained and restrictive isolation. For example, I set the JDBC connection on 'read uncommited' which is just fine for 4 of the 5 tables. However, for dealing with table 5 I need serious locking, so only for the queries which operate on table 5 I specify more restrictive isolation in the queries.

I search a lot on the internet, but nowhere I can find a *real* explanation for my questions. Only pieces of the puzzle..any comment on this will be greatly appreciated !
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 60810

"Guru Java",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Forum Bartender

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Guru Java

Joined: Jul 26, 2005
Posts: 2
Thats my real name, I cant help it :-)
I'll change it
I agree. Here's the link:
subject: JDBC isolation level vs query
Similar Threads
best programming practises of JDBC
Dirty read (?) problem
Row locks and transaction levels
Retrievals - Read Only Entity Bean Vs Customized Retrieval Method
hibernate with postgres - deadlocks