Re: serializable isolation level behavior question

From: <hasta_l3_at_hotmail.com>
Date: 19 Oct 2006 22:47:11 -0700
Message-ID: <1161323230.928976.262350@e3g2000cwe.googlegroups.com>

Bob Jones wrote :

> <hasta_l3_at_hotmail.com> wrote in message
> >
> > The OP transaction performed casher DML statements.
> > It had transaction level serializable.
>
> No, the session that was serializable was doing a truncate that is NOT a
> DML.
>

Well, the way I read the OP description copied below, the serializable transaction (in session 2) issued only selects.

---

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? Received on Fri Oct 20 2006 - 00:47:11 CDT