File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Scenario where one should use Read committed or Serializable as Isolation level? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Scenario where one should use Read committed or Serializable as Isolation level?" Watch "Scenario where one should use Read committed or Serializable as Isolation level?" New topic
Author

Scenario where one should use Read committed or Serializable as Isolation level?

scott miles
Ranch Hand

Joined: Jun 16, 2011
Posts: 70
I am trying to figure out which isolation level (among serializable and read committed )is better in what scenarios..At link http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#i17894, I was going thru topic choice of isolation level, I got some clarity and some questions based on some statements in the article.

Satement :- Read committed isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results due to phantoms and non-repeatable reads for some transactions.

Question1:- How Read committed isolation provides more concurrency than serializable?As per myunderstanding serializable transactions also does not restrict concurrent transactions.



Statement:- All queries in an Oracle serializable transaction see the database as of a single point in time

Question:-I think what they mean here , when serializable transaction begin say at time t1 then all the data will be presented from the state of Database which was at time t1.Right? Not sure when we call the transaction actually begins. Is it when we get the connection or when first query is fired?

Statement:- Oracle's serializable isolation is suitable for environments where there is a relatively low chance that two concurrent transactions will modify the same rows and the long-running transactions are primarily read only

Question:- Say two transactions tran1 and tran2 begin at time t1. If tran1 updates the the row1 at time t2 and later at time t3 tran2 fetches the same row it wont get the updated row done by tran1 because tran2 will fetch the state of data which was present at time t1.Right?

Statement:- Coding serializable transactions requires extra work by the application developer to check for the "Cannot serialize access" error and to undo and retry the transaction.

Question:- Not sure when developer will get “Cannot serialize access” error. Will we get the same error in below scenario

Say two transactions tran1 and tran2 begin at time t1. If tran1 updates the row1 at time t2 and later at time t3 tran2 updates the same row1. Will it throw the “Cannot serialize access” error in this case? If yes Does oracle maintain the version internally in case of serializable transactions so that it gets to know row has been updated by user?


Would appreciate if someone can brief the answer against each question. Thanks in advance.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

1) In Oracle, readers never block writers and vice versa. That is true even for serializable transactions. However, Serialiable isolation level does impose stricter rules than Read commited level. When these rules cannot be met, the error ORA-08177 can't serialze access is generated.

The statement about concurrency regards the following: in situation where two Read committed transactions succeed, Serializable transactions doing the same might fail with the ORA-08177 error. That hinders concurrency, of course; even if you replay the transaction successfully, it takes longer time and consumes more resources.

In more detail: even updates in read committed transaction might find out that the data it is about to modify were modified by a different transaction. In this case, such update command is restarted automatically and transparently by Oracle. However, as Oracle cannot restart the whole transaction (there can be a processing outside the database it doesn't know about), its up to you to restart processing when using Serializable transactions.

2) The transaction in Serialiable isolation level begins either with the SET TRANSACTION ... statement, or - if session isolation level is set to Serialiable - with the first DML statement (simple select is also regarded a DML statement).

3) Right.

4) Generally speaking, yes to both questions. The underlying mechanism might be a bit different (I'm not really sure about it), but conceptually it works the way you state.

I'd suggest to take a look on asktom.oracle.com, there are several very valuable and very detailed threads regarding Serializable transactions (as well as nearly every other Oracle feature).
scott miles
Ranch Hand

Joined: Jun 16, 2011
Posts: 70
Thanks Martin for such a elaborate reply. Ness some more clarifications on reply

AS you told even updates in read committed transaction might find out that the data it is about to modify were modified by a different transaction. In this case, such update command is restarted automatically and transparently by Oracle. However, as Oracle cannot restart the whole transaction (there can be a processing outside the database it doesn't know about), its up to you to restart processing when using Serializable transactions.

Can you give a brief scenario/little more explaination(or small code snippet explaining this) where transaction is restarted automatically and transparently by Oracle in case of l read committed while Oracle cannot restart the whole transaction and its up to usto restart processing when using Serializable transactions. As per my understanding in case of read commited transactions, its the developer who has to find out whether data got updated in between(with the help of some version column in table) anf if it got updated, developer generally throws concurrent modification kind of exception. so i did not get what do you mean by even updates in read committed transaction might find out that the data it is about to modify were modified by a different transaction. In this case, such update command is restarted automatically and transparently by Oracle.

Thanks fa lot or bringing clarity to other queries.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

Here is an excellent thread regarding update restarts. You very probably don't need to go into such detail, I've just included it in my post to note that the same situation that causes ORA-08177 error may occur in Read commited isolation level, but is silently handled by Oracle. I should have probably waited till you would ask "Wait, what if this update conflict happens in Read commited transaction? Why don't I have to handle ORA-08177 there?".

You would need to know this detail only if you have a long running update with lots of smaller updates conflicting with the large one, that is not very common in most systems. If you don't deal with this kind of situation, you can safely forget about this whole mechanism.

Can you give a brief scenario/little more explaination(or small code snippet explaining this) where transaction is restarted automatically and transparently by Oracle in case of l read committed while Oracle cannot restart the whole transaction and its up to usto restart processing when using Serializable transactions.

For the explanation and examples see the above link. Note, however, that Oracle never restarts a whole transaction. It cannot, there might be a logic hidden somewhere it didn't see. This is why you have to handle ORA-08177 yourself and do whatever you deem necessary to do; in most cases, replaying the transaction is perfectly fine. As already mentioned, in read-committed level ORA-08177 does not occur and you don't have to handle it; Oracle handles these situations on it own.

As per my understanding in case of read commited transactions, its the developer who has to find out whether data got updated in between(with the help of some version column in table) anf if it got updated, developer generally throws concurrent modification kind of exception.

This probably does not have anything to do with the situation described above. I assume you have optimistic locking on your mind. This technique is described elsewhere and as far as I know, it is database independent, there is nothing specific to Oracle when implementing optimistic locking.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Scenario where one should use Read committed or Serializable as Isolation level?
 
Similar Threads
Transaction Isolation Level
Transaction Isolation Levels
Data concurency
Question about TRX_REPEATABLE_READ
JDBC manually commit