IDS Forum

Re: Locks

Posted By: ART KAGEL, BLOOMBERG/ 65E 55TH
Date: Tuesday, 21 January 2003, at 2:00 p.m.

In Response To: Locks (ELENA GROVER)

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.

Messages In This Thread

• Locks
  ELENA GROVER -- Tuesday, 21 January 2003, at 12:30 p.m.
  • Re: Locks
    ART KAGEL, BLOOMBERG/ 65E 55TH -- Tuesday, 21 January 2003, at 2:00 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.