Problem with Oracle's Txn Isolation Level

From: <tncc>
Date: Sun, 16 Jan 2005 12:07:59 +0800
Message-ID: <41e9e8a1$1_2@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. Received on Sat Jan 15 2005 - 22:07:59 CST