Re: Timestamp Column. -Reply

In article <354c4d56.5004453@gate.idg.no>, Nils Myklebust <Nils.Myklebust@nmdata.com> writes >On Sat, 2 May 1998 04:37:04 +0100, David Williams ><djw@smooth1.demon.co.uk> wrote: > >No, David, still not good enough. >The senario is this: > >User 1 selects a row - version_id = 10 >User 2 selects the same row - version_id = 10 >User 2 updates row and sets version_id = 11 >User 1 wants to update the same row, but finds the version_id >different (allways higer) from his value of 10. The update should be >refused. He should have to read the row again and use the new User 1 would refuse it as he reads the row again using select for update (getting a exclusive lock). He then finds that the version_id is higher than before indicating that someone else changed the row. The application should then error saying that someone else has updated your row... >version_id he then finds in another attempt to update the row. >Remember we are optimistic here, so only in the most extreme cases >would he have to try this more than ones. If that happens to often >this kind of optimistic locking isn't very usefull. > >In your solution you have a transaction that does nothing but increase >the version_id. If you moved the transaction outside this function it This indicates that the row is 'locked' and means that when other users go to update the row they will find that the version_id has changed and so not update the row. The combination of an exclusive lock to update the verison id and an exclusive lock to check the version id provides mutual exclusion. This also means that locks are not 'held' i.e. other users can read the row whilst it is 'locked' i.e. big reports can still run. Also there is no problem with long transactions or crashed sessions holding a lock permanently!! By george! I think he's cracked it! >might work. In that case it might be better to implement the function >differently though. >You still have problems: What if someone doesn't use this system in >some application? The others relying on it would be out of luck. > True. But that applies to any version_id solution. Applications have to check the version_id. Perhaps this should be a feature request?? >To me the use of a where clause in the update statement including all >fields in the table or all fields to be updated is a better solution >untill Informix takes it upon themselves to implement a true >version_id system within the database. I believe that can be done. I However what happens when somebody updates a field not in the where clause? This could still be a field displayed on the screen to the user and the value in that field may be why the user entered a particular new value on the screen. E.g. payment for a job. User 1 select job details (including emergency flag) from job where job_no =123; and display on screen. user thinks It has the 'E' for emergency flag set an so I'll enter a charge for the job of 100 rather than 50 (double charge). User 2 updates the emergency flag to 'N' for no emergency. Update job set emergency = 'N' where job_no = 123; user1 does Update job set charge = 100 where job_no = 123; This succeeds even thought the original reason for the 100 charge it no longer true. Basically if you update an entity you must be sure that none of the entities attributes have changed and NOTHING RELATED to the entity which may affect decisions about new values for the entity's attributes have changed. Ok I'm being strict but a full solution is hard and I'm still looking for it!! >also believe it can be implemented via triggers and stored procedures >if Informix allowed such a stored procedure to update rows that where >included in the update statement that fired the trigger. I also belive >that the update to a row with such a version_id has to include the >value of that version_id as it was read via a previous select >statement and that no updates to such a row (table) can be allowed >unless that is the case. > >Even if you have such a version_id system there are cases where some >columns may be updated by one user and others by another user >independently of each other even on the same row. In this case only >the system with a where clause with the appropriate columns included >would work. That just may be a better solution over all as well. > True! But see the idea of related entities above. Sometimes application screens can contain attributes from several entities at once! > >> Ooops! Thought that submit meant add a new record!. >> >> Well, what you really mean is a version_id on each row. Easy >> >> >>FUNCTION get_version_id(primary_key,primary_key_value,tablename) >> >> DEFINS str1,str2 CHAR(100) >> DEFINE version_id INTEGER >> >> LET str1 = "UPDATE ",tablename CLIPPED, >> " SET version_id = version_id + 1 WHERE ", >> primary_key CLIPPED," = ",primary_key_value CLIPPED >> LET str2 = "SELECT version_id FROM ",tablename CLIPPED, >> " WHERE ", >> primary_key CLIPPED," = ",primary_key_value CLIPPED >> >> BEGIN WORK >> PREPARE P1 FROM str1 >> PREPARE P2 FROM str2 >> EXECUTE P1 >> DECLARE CURSOR C1 FOR P2 >> OPEN C1 >> FETCH C1 INTO version_id >> FREE C1 >> FREE P1 >> FREE P2 >> COMMIT WORK >> RETURN version_id >> >>END FUNCTION > >Nils Myklebust >NM Data AS >Norway >E-mail: Nils.Myklebust@nmdata.com >FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html >(Now with ODBC info under "Third party products".) -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care