Re: Best Method for Updating a record

One way to achieve what you want is to add an Informix time-stamp field to the relevant tables. A resolution of frac(3) should be more than enough. Of course this method is cumbersome in that you have to update the time-stamp manually but it is fairly cheap and reliable. Achim Reiners (ar@mai.de) wrote: : Well, guys, I'm thinking of a methodological problem: : I'm working with Informix just for some weeks (SYBASE before). : How to do updates to a table in respect of concurrency in the best way? : Assume in a multi-user environment an application reads and eventually : updates a table A. The sequence is simply to read the record, let the user : edit it and, after confirmation by the user write it back or not. : The record shall not be rewritten if the record changed in between! : There are some solutions to do this: : 1.) Lock the record from the time it is read until it is rewritten. : This has as I think some problems: : a.) The first is that the record is locked for a long time if the : user takes a coffie break or something else without unlocking : the record. So in this case other applications can not access : the record may be for a long time. : b.) On the other hand, what happens if the user`s application fails : before unlocking the record? : c.) The application probably already at reading time has to know if : writing has to be done (maybe records are locked that will not : be rewritten). : 2.) Do not lock the record! : When rewriting the record, do a "full specification" of the old one. : I mean, to be sure that noone else has modified the record in between, : add a WHERE-clause to the Update that will fully specify the old record : and so will not do the update if any value was modified. : Here we have the overhead of remembering all old values in the application. : It is the optimistic method that normally only 1 user works updating : with 1 record at a time. : 3.) Is there any easier method to 2.) without remembering the : "full specification" of the old record like the usage of a : timestamp in SYBASE? Has Informix a datatype like SYBASE's TIMESTAMP that : changes on any update of a record? : Probably there are some different methods varying from the basic ones. : Of course, the whole environment in respect of performance, safety and the kind : of data has to be regarded for a decision of the method. : I would be pleased to hear anything about *your* experience and ideas : to this problem here in the newsgroup or by E-Mail. : Thanks, : Achim : -- : ========================================================================== : / Achim Reiners Software Engineering \\ : | /M/A/I Deutschland GmbH | : | Softwarezentrum Koeln Phone: +49 221 956400-40 | : | Mathias-Brueggen-Str. 85 Fax: +49 221 956400-69 | : \\ 50829 Koeln E-Mail: ar@mai.de / : \\_________________________________________________________________________/