From: raghu@.informix.com (Raghu Ramabadran)
Newsgroups: comp.databases.informix
Subject: Re:Implementing Referential Integrity ( Possible in Oracle ?)
Date: 15 Apr 92 23:10:07 GMT
Organization: Informix Software, Inc.
Originator: raghu@godzilla

____________________________________________________________________
From infmx!pyramid!gossip.pyramid.com!olivea!uunet!iWarp.intel.com|inews!mipos2.intel.com!kortikar Wed Apr 15 15:29:22 PDT 1992

Consider following tables

Item-vendor table				Vendor Table
-----------------------				----------------------------
Item_code,vendor_code				vendor_code,name,address etc.

Following rules have to observed

1. Vendor can not be deleted if used in item-vendor table.
2. every vendor in item-vendor table must be present in vendor table.

Rule 1 - can be enforced by putting appropriate triggers (key-delrec etc)
	 in data entry programs of vendor table.

Rule 2 - this is a problem issue.


Consider following scenario

Time 	User1 Action			User 2 Action

10:30   Selected Add data 
        For Adding Data into 
	Item-vendor table

10:31  Checked existence of vendor     
       code v1

10:33					deleted vendor v1
					(Succedded because there was
  					 no entry in item-vendor table)

					Commited.
10:35  commited transaction.

Now item-vendor table contains vendor which does not exist.

Checking the reference at commit time also DOES NOT solve the problem, because
there is still a time gap however small between two statements which check the
existence of reference and commiting of data. (essentially the refrence checking
and commit is not atomic.)

If my understanding is correct Oracle does not allow me to put lock on a row
so that user 2 will not be able to delete it. (select for update type of lock
is no good becuase then TWO people can not add different items for same vendor)

I need a lock of type xxx that will prevent write or delete or update but will
allow another lock of type xxx (or any number of those.), hence the delete has 
to fail if ANY occurance of xxx type of lock exists.

there is no direct way of doing this by using oracle supplied lock(s).

Is my understanding correct or am I missing something.

(In any case how do other major RDBMS's handles issue of refrence checking ?)

Thanks In Advance. 

kortikar (kortikar@mipos2.intel.com)
____________________________________________________________________



____________________________________________________________________
From infmx!pyramid!gossip.pyramid.com!olivea!uunet!think.com!rpi!raghavav Wed Apr 15 15:29:49 PDT 1992

In article <10414@inews.intel.com> kortikar@mipos2.intel.com (Aniruddha Kortikar) writes:
>Consider following tables
>
>Item-vendor table				Vendor Table
>-----------------------				----------------------------
>Item_code,vendor_code				vendor_code,name,address etc.
>
>Following rules have to observed
>
>1. Vendor can not be deleted if used in item-vendor table.
>2. every vendor in item-vendor table must be present in vendor table.
>Rule 1 - can be enforced by putting appropriate triggers (key-delrec etc)
>	 in data entry programs of vendor table.
>Rule 2 - this is a problem issue.
>
>Consider following scenario
>
>Time 	User1 Action			User 2 Action
>
>10:30   Selected Add data 
>        For Adding Data into 
>	Item-vendor table
        ^^^^^^^^^^^^^^^^
>>	Assume you are adding v1. For any database system which uses two phase
>>locking. It will have to do a read on on Vendor to verify existance of v1. 
>>Thus placing a lock on it. This will prevent User 2 from deleting it. Check
>>out Phantom problems in any good book. User 2 should not be able to delete.

The concurrent operation is dependent upon the database isolation level.There
is a standard called SQL2 which usually RDBMS vendors adhere to.The level of
isolation for read operations within a transaction is determined by an 
isolation level which is set via SET TRANSACTION statement.The default level
should be specified in the manuals ( it varies depending upon whether database 
is created with logging , database is ANSI compliant ..).

Different levels of isolation are

level 0 : Allows dirty reads,nonrepeatable reads, phantoms

level 1 : allows nonrepeatable reads and phantoms

level 2 : allows phantom

level 3 : all transactions are serializable


In your case when user1 has verified the existence of vendor , if the isolation
level is set to 3 then the user2 will not be allowed to delete vendor.but for
any other isolation level user2 can insert a 'phantom' record.

_raghu_

P.S:standard disclaimers apply