Re:Implementing Referential Integrity ( Possible in Oracle ?)

____________________________________________________________________ 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