Re: Timestamp Column. -Reply

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 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 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. 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 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. > 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".)