This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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 table.name 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 !