Re: L:ist - Can do/do better in MS SQL than Oracle

On Thu, 4 Apr 2002 21:37:18 +0100, "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:

>the set transaction isolation level serializable example is dead on - it
>don't work in oracle. Of course the actual design for the simplified eg is
>terrible ; withdrawal of funds from a net balance in two different tables
>and update a third withdrawal table wtf? however I tried the example as
>stated and broke the business rule (coding to follow - its at work not
>home). maybe if there were a real life eg where this mattered i'd care
>more....

In the financial world it is fairly common to add information about financial transactions as rows and not updating existing rows. This can break business rules. (No predicate lock exists in Oracle).

A practical example we came across.
A master record which can have details. Deleting of the master is done by using a status field, (setting this status field to not_valid for example). This can only be done if there are no details for this master.
Another process can write a detail at te same time a masterrecord is made not_valid.

Removing a masterrecord
Within the transaction a check is made for existing details. If there is none the master can be made not_valid.

Adding a detailrecord
Within the transaction a check is made for existence of a valid master record while adding a detail.

This can lead to breakage of the business rule.

One can make a constraint on the master detail relationship but if this does not include the status field it does not work. If this includes the statusfield then there is a statusfield in the detail always containing the same value. (Rather silly to me).

So to solve this problem we will refert to an application solution.

ben brugman

>
>
>--
>Niall Litchfield
>Oracle DBA
>Audit Commission UK
>
>

Ben Brugman Received on Fri Apr 05 2002 - 07:34:57 CST