Locks

I am just starting to use Informix and I have read a lot of documentation about locking but I am more interested in best practices. This question is not specific but any recommendation or personal experience is welcome. My problem is the next, suppose that I have a table call clients with about 3 millions of records, this table is actualized frequently and a lot of reports are run against it. Is the best option to use a row lock? If some user 'A' is consulting a record and some other user 'B' modifies the same record what can I do to refresh the record for the user ‘A’? How can I assure when user 'A' and user 'B' are trying to modify the same record at the same time that an update would be always against the new values of the record and not the values before modification for another user? How can I get the best performance, high availability and assure data consistency, using locks? Talking about 50-100 concurrent users, tables about 1-10 million of records and using ANSI DB. Thank you very much.

Best practice it to implement an optimistic locking protocol. Each table that is actively updated should have a timestamp column defined as DATETIME YEAR TO FRACTION(5) and if the table is to be fragmented create it WITH ROWID and you should run the instance with USEOSTIME set to one (1) so that the fractional seconds will be as accurate as your OS can make them (with USEOSTIME 0 the engine provides only whole second resolution to the CURRENT function). When you fetch a row for displaying for update DO NOT lock the row but fetch the timestamp and ROWID columns along with other relevant data. When the user wants to save changes fetch the current timestamp using the ROWID with the FOR UPDATE clause which will lock the row (yes use ROW locks not PAGE). Compare this timestamp with the original one and if they are the same then procede with the update, if they differ abort the update and either display the changed row and ask the user to apply the changes again, ask the user what to do without redisplaying first, or you could fetch the entire row to see if the modifications will clash with this users mods. This way no locks are held in case the user gets a phone call or goes to lunch. An UPDATE trigger is used to maintain the timestamp so that even manual updates performed in dbaccess will modify the timestamp and the initial value is set by a DEFAULT CURRENT clause in the timestamp column's definition when the table is created. All reports should include a SET LOCK MODE TO WAIT <nseconds>; statement so that transient locks due to updates being committed will do not cause the report to fail with a lock error. Reports that have to be AS OF the time the report was started should be run using ISOLATION LEVEL REPEATABLE READ reports that must complete but must report on consistent data must use COMMITTED READ isolation. Art S. Kagel ----- Original Message ----- From: Elena Grover <mgroveres@yahoo.com> At: 1/21 13:29 > I am just starting to use Informix and I have read a lot of documentation about > locking but I am more interested in best practices. This question is not > specific but any recommendation or personal experience is welcome. > > My problem is the next, suppose that I have a table call clients with about 3 > millions of records, this table is actualized frequently and a lot of reports > are run against it. > > Is the best option to use a row lock? If some user 'A' is consulting a record > and some other user 'B' modifies the same record what can I do to refresh the > record for the user A ? How can I assure when user 'A' and user 'B' are trying > to modify the same record at the same time that an update would be always > against the new values of the record and not the values before modification for > another user? > > How can I get the best performance, high availability and assure data > consistency, using locks? Talking about 50-100 concurrent users, tables about > 1-10 million of records and using ANSI DB. > > Thank you very much.