Re: Problem with Oracle's Txn Isolation Level

<tncc> a écrit dans le message de news:41e9e8a1$1_2_at_rain.i-cable.com...
| i am a bit confused by the isolation level of Oracle.
|
| I read the online documentation (9i Database concepts). I know Oracle't txn
| can be either be read only, Read_committed or Serializable. However, I
| still cannot get a concrete understanding of the EXACT level of service
| guaranteed by Read Committed and Serializable. The description based on
| Non-repeatable Read, Phantom Read can give me a GENERAL understanding. But
| what I need - as a programmer - is the precise definition of the level of
| service guaranteed. This is just like an API specification guarantees a
| function's return types & values are whatsoever precisely and required
| whatsoever input parameters.
|
| Take for example, how can i know what transaction isolation precautions
| should I take in the
| following code:
| ======(TRANSACTION A)
| 1. INSERT INTO table_a (col1)
| SELECT col_a
| FROM table_b
| WHERE insert_flag='false';
|
| 2. UPDATE table_b SET insert_flag='true'
| WHERE insert_flag='false';
| =====
| i.e. insert those rows in table_b into table_a and set the insert_flag so
| that i know which rows
| has been inserted.
|
| However if these codes of transaction B is executed and committed
| concurrently between
| line 1 & 2 above:
| ====(TRANSACTION B)
| 1. INSERT INTO table_b (col_a, insert_flag)
| VALUES (1, 'false');
| 2. COMMIT;
| ====
| Without txn isolation, the above inserted row would be marked as
| insert_flag='true' but without
| physically inserted into table_a.
|
| Now I know txn isolation is what to prevent these problems. But what is the
| exact definition
| of behaviour if I use Read Committed in TRANSACTION A? And, what would
| happen if I use Serializable?
| The Database Concepts only provide a general description of the issues but
| didnt provide much help
| as to how the code should looks like.
|
| If I use Serializable, I know i will occasionally receive 'can't serialize
| access error'. But what
| should my code do at that time?
|
| Anyone can provide me a pointer to where more referece materials are
| available.
|
| Thx in advance.
|
|

In simple words:

• with read committed (Oracle default) transaction level, you work with an image of the database as it were at the beginning of the *query*.
• with serializable transaction level, you work with an image of the database as it were at the beginning of the *transaction*.

Conclusion?

Regards
Michel Cadot Received on Sun Jan 16 2005 - 00:42:15 CST