Re: serializable isolation level behavior question

<hasta_l3_at_hotmail.com> wrote in message news:1161414027.408546.305470_at_i42g2000cwa.googlegroups.com...
> Bob Jones wrote :
>
>> >> >
>> >> > 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.
>> >
>> >
>>
>> Okay, I got it backwards, but it still makes no difference to the
>> argument.
>
> It changes a lot, in my opinion, Bob.
>
> Let's wear the hat of the innocent Oracle user/developer.
>
> We write a nice serializable transaction, that uses only casher DDL.
>
> Being good programmers, we envision that Oracle sometimes raises
> all sorts of errors, and make sure that our transaction is rolled back
> if one happens.
>
> Now, assume that while our innocent, well-written code is running
> our transaction, somebody else truncates the temporary table we
> are using, perhaps by error.
>
> And ***horror***, our transaction does not get an error. It does not
> get rolled back. It continues quietly, with wrong data, perhaps
> printing out a wrong financial report, or storing in the database
> incorrect clinical data.
>
> This is just plain wrong !!!
>
> You know, one of the things I like most with Oracle is its
> philosophy - and reality - that either its promises to a transaction
> are fulfilled, or an error is raised.
>
> But the behavior here discussed is just not on par. I dont care
> about serializable transactions. But I do care about the philosophy,
> because my data depends on it.
>
> Have a nice day.
>

Yes, it is a business philosophy indeed.

1. If you are running a transaction and at the same time someone else is doing a destructive DDL, that tells a lot about the design and security. Your data could be wrong even before the transaction began. Serializable is not the solution.
2. If you do not want anybody else to DDL the table during your transaction, lock the table. Serializable only works when all parties are performing transactions. That was why I suggested using delete.