Re: Timestamp Column. -Reply

In article <3548668e.12307265@gate.idg.no>, Nils Myklebust <Nils.Myklebust@nmdata.com> writes >On Thu, 30 Apr 1998 02:39:25 +0100, David Williams ><djw@smooth1.demon.co.uk> wrote: > >>In article <6i8aik$3eo$1@news.xmission.com>, Robert Wines >><Robert_Wines@ama-assn.org> writes >>> >>>I would not recommend using datetime as a timestamp. Given the speed >>>of SMP systems it is possible that someone can submit a record during ^^^^^^ >>>.001 and someone else submit a record at .0019. ^^^^^^ 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 >>> >>>I would suggest you use an integer, start a transaction that locks the >>>row, compares the integer, make whatever updates you need then >>>increment the integer and release the lock. >>> >> i.e. a serial field!! > >No, David, this is definitely not a serial field. It's a "serial" on >the same record. >I believe some database engines have this capability built in. At >least it would have been a nice feature. >The problem is optimistic locking. If someone reads a row from a table >and displays it for user update on a screen you normally don't want to >lock the record while the user is doing the changes. Later the user is >finished and the updated data is to be written back to the same row. >How do you find out that noone else has updated the row in the >meantime? >You can use what is unfortunately called a timestamp field for this. >Every time a row is updated the timestamp is changed. When one user >tries to write back the row, if the timestamp is changed you know >someone else updated that row in the meantime. This update should now >be refused and you can take any action you want in the program >(display the differences and let the user deside or whatever - many >options are possible in different situations). >It's unfortunate that such a field is called a timestamp. Many believe >it is some kind of a datetime field. It shouldn't be however. It >should simply be an integer that starts out be 0 or 1 when a row is >first inserted and is then automatically incremented by one by the >database engine every time that same row is updated by someone. A >simple implementation would let you do an update where you used the >same value for such a field in the update statement as you had >previously read from the database. Then the engine would refuse the >update if that value had changed when you executed your update >statement. If it isn't changed the update is done, but with an >increment of 1 to this field. > >You might have done this via a trigger and stored procedure. However >that isn't possible as such a stored procedure isn't allowed to update >a field that is also updated by the update statement that fired the >trigger. May be some other scheme is possible to implement via >triggers and stored procedure to solve the problem. > >Another alternative for uptimistic locking is allways to use a where >clause that include all fields (or all fields to be updated) to check >if someone else have done an update in the meantime. Somewhat harder >to implement if your language doesn't have it built in, but not realy >difficult. > >Optimistic locking is so important in many applications I do think >Informix should have done something to make it simple. > > >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