serializable isolation level behavior question

From: <harvinder76_at_gmail.com>
Date: 16 Oct 2006 13:40:34 -0700
Message-ID: <1161031234.430942.187950@m7g2000cwm.googlegroups.com>

Hi,

We are testing concurrency performance and trying to isolate the issue using sqlplus and some dummy tables and need some clarification. Following is the scenario:

Session 1 (isolation level read committed)

Insert into dd values(1);
Commit;
Insert into dd values(2);
Commit;

      Session 2 (isolation level serializable)

      Set transaction isolation level serializable

      Select * from dd; (2 rows returned at this point)

Now issue another insert from session 1
Insert into dd values(3);
Commit;

Now run the select again from session2:
Select * from dd; (still return 2 rows)

Now truncate the table from session1:
Truncate table dd; (issue implicit commit)

Now again run the following select from session 2:

Select * from dd; (This should still shows 2                rows as

read data from rollback segments since in serializable mode, it need to show the pre-image before the truncate but it is showing 0 rows)

Is this expected behavior that in second session it returns 0 rows and not 2 rows?

Thanks
--Harvinder Received on Mon Oct 16 2006 - 15:40:34 CDT