<hasta_l3_at_hotmail.com> wrote in message
news:1161496075.538896.189350_at_e3g2000cwe.googlegroups.com...
> Bob Jones wrote:
>> <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.
>
> Of course, there is no way to fully protect against mistakes.
>
> But good design, good security *and* good error detection from
> software components - including Oracle ! - together go a long way
> to mitigate the risks.
>
The DBMS software can only go so far by providing the necessary tools. It is
just a matter of whether they are used appropriately.
>> 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.
>
> Agreed, but we are not discussing whether the transaction should
> complete successfully after a DDL, Bob. Everybody agrees it should not.
>
In a way we are. We would not have this discussion if the DDL failed.
> We are discussing whether an error should be reported by Oracle.
>
> And IMO it should.
>
Actually we were discussing about standard compliant. So far it has not been
proven it is not. Should Oracle generate a error? It does not have to
because "serializable" makes no sense to non-transactions.
> Because pretty no application wants DDL to be performed against tables
> it is using. And nevertheless these applications do *not* lock their
> tables,
> for concurrency reasons.
Not sure what you meant by "these applications". Only OP's simple select
statement does not lock the table.
> They all rely on Oracle to either prevent the
> DDL or report an error when the invariants are no longer true.
>
> And that's as it should be.
>
Oracle cannot do it unless instructed to with the right code.
Received on Sun Oct 22 2006 - 12:47:48 CDT
