IDS Forum

Re: locking and index

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Thursday, 18 May 2006, at 8:33 a.m.

In Response To: locking and index (Support Informix)

Yes, if your table is set for ROW level locking then individual keys are locked.
The key in the index being used for seaching for updates or record scanning.
For inserts all of the keys on the row are locked in all indexes and for updates
all of the keys that have to be modified as well as any select key are locked.

As to lock timeouts, these are due to the affected session using SET LOCK MODE
TO WAIT <n>; and 'n' seconds have passed without a particular lock having been
released. There is no server-wide lock timeout. If a session does not set lock
mode to wait then lockouts are instantaneous and return a different ISAM error
code. So, either the timeout values in the apps has been reduced or some new
or modified application is holding locks longer. Look for some interactive app
that is doing pessimistic locking, ie holding a lock on a modified record
while waiting for an interactive user to commit the transaction. These are
deadly to concurrency. Interactive apps should only use optimistic locking
protocols.

Another possibility is an app that's performing a large (as opposed to long)
transaction spanning many rows and committing the work as a single block
transaction. These may run OK for many years until the volume of data they have
to operate on exceeds some threshhold at which point the duration of their
transaction begins to cause lock timeouts.

Art S. Kagel

----- Original Message -----
From: Support Inf.... <ids@iiug.org>
At: 5/18 7:06:47

Can someone explain me the locking mechanism on indexes ?
If the table is on row lock mode, are the indexes always row locked ?
Or are there other considerations ?

We are in ID7.3, we plan to migrate to IDS10.
Is there a difference about locking with indexes attached or detached from
tables.

We have more and more 'lock timeouts'.
A suggestions is to change the lock timeout from 30secs to 60secs.
I remember having read something about, but can't find out where.

Yves

*****DISCLAIMER*****
Dit bericht en alle bijhorende zijn uitsluitend bestemd voor de geadresseerde
en
vertrouwelijk. Indien dit bericht niet voor U bestemd is, gelieve dit dan te
vernietigen en
de verzender te verwittigen. Openbaring, vermenigvuldiging, verspreiding en
verstrekking aan
derden is niet toegestaan, tenzij anders vermeld. Aangezien internet de
integriteit van dit
bericht niet kan verzekeren, kan de Dienst Vreemdelingenzaken niet
verantwoordelijk gesteld
worden indien dit bericht gewijzigd is.
Bezoek onze website: http://www.dofi.fgov.be

----------------------------------------
*****DISCLAIMER*****
Ce message et toutes les pieces jointes sont etablis a l'intention exclusive
de ses
destinataires et sont confidentiels. Si vous recevez ce message par erreur,
merci de le
detruire et d'en avertir l'expediteur. Toute utilisation de ce message non
conforme a sa
destination, toute diffusion ou toute publication, totale ou partielle, est
interdite, sauf
autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de
ce message,
l'Office des Etrangers decline toute responsabilite au titre de ce message,
dans l'hypothese
ou il aurait ete modifie.
Visitez notre site web: http://www.dofi.fgov.be

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• locking and index
  Support Informix -- Thursday, 18 May 2006, at 7:03 a.m.
  • Re: locking and index
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 18 May 2006, at 8:33 a.m.
  • Re: locking and index
    gerd kaluzinski -- Thursday, 18 May 2006, at 10:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.