Re: Locking on Informix

Art's suggestion is fine as long as you make sure all applications do this type of update. Of course you have the same problem with your use of a datetime field. The datetime is much worse though as whatever fraction of a second you use (whatever is available) it would allways be possible to have two updates within the same fraction. If not now then on some new faster hardware in the near future. What we do is an update allong these lines: update tablename set ... where primarykey = ? and col1 = ? and col2 = ? and so on either for all columns in the table or more commonly for some subset. The subset may be the columns updated or sometimes some additional columns. Never less than the columns updated of course. The values put in for the ? are the values retreived with the original select used to populate the screen fields for the user to update. Of course if any of the columns where null you have to replace the = ? with "is null". If this is the case the update statement have to be dynamically created and executed (not prepared ones and only executed) every time it's used, which can be a pain. After this we test for the number of rows updated. If it's 1 nothing had been changed while the user did the changes on screen. If it's 0 something changed. You may then possibly do a select again to present the changes to the user or do whatever other action you deam necessary. This doesn't work very well if you update the primary key, but you don't do that, do you...? "Art S. Kagel" <kagel@bloomberg.com> wrote: :infohos wrote: :> :> Centura 1.1.1. PATCH 2 :> NT-Client 4.0 :> Informix On-Line 7.22C1 :> Windows NT 3.51 :> :> We are using Centura 1.1.1. on an Informix On-Line Database. :> We added an extra field on every table to check the locking. :> That field contains the current (03-12-1997 12:30:25.000). :> The moment that you try to do the update, we are checking :> if another user has changed the record since we have that :> record on the screen using that datetime field. :> If the record has been changed, the user gets a warning :> on the screen : Record has been changed by another user ! :> Windows NT never gives you the fractions of seconds. If :> two users do the update on the same second, the system :> doesn't give you the warning ! :> :> How do you check locking on Informix ? : :You can always use an integer column instead and mark it with: : :UPDATE mytable SET (testcolumn=testcolumn+1) where .... : :this would guarantee that if another program has modified the row the :value will differ. : :Art S. Kagel Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org