• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
reply
    Bookmark Topic Watch Topic
  • New Topic