In article <1108064130.703093.220050@l41g2000cwc.googlegroups .com>,
pmenegh_jr@pop.com.br wrote:

> Hello,
>
> I have a database integrity problem described in the following example:
>
> 1. There is a situation of Parent X Child records.
> 2. User A is creating the parent records.
> 3. User B, in another session, is creating the child records. User
> B can create child records only for the parent records that user A have
> created. The program user B uses is validating whether the parent
> records exist.
> 4. User A has created a parent record but hasn't commited the
> transaction yet.
> 5. User B finds the parent record created by user A (the parent
> record is in a transaction which isn't commited yet). User B commits
> his transaction. The child record is commited to the database.
> 6. User A cancels the transaction of the parent record. As user B
> had created a child record, there is a child record without a parent
> record. There is an integrity problem.
>
> This is just a case for example. The programs are much more complex and
> there is a lot of programs. So I am looking for an alternative solution
> for changing every single program for locking records.
> It seems to me that the best solution would be a session startup
> parameter which would cause the following behavior:
>
> 1. If a transaction isn't commited, the changes done during this
> transaction can be perceived only inside the sections the changes were
> done.
> 2. If a transaction has been committed, automatically all its
> changes can be perceived for all the sessions of all users accessing
> the database.
>
> Thanks in advance,
>
> Paulo

You really need to do this with EXCLUSIVE-LOCK to eliminate that problem.