SEO

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

This situation shows one of the uses of the much-maligned share-lock. With
a share-lock (as opposed to no-lock), user B won't be able to retrieve user
A's parent record. You can choose to have user B return a "doesn't exist"
validation error without hanging by using "find .... share-lock no-wait
no-error".

Really roughly, no-lock gives you "read uncomitted", and share-lock gives
you "read committed" isolation. Share-lock fell out of fashion a long time
ago due to the old 4GL style of programming where transactions remained open
while waiting on UI, and no-wait wasn't available before V6 (I think), but
share-lock behaves very much like data accesses on Oracle or SqlServer.

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.