Re: Timestamp Column. -Reply

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