locking and index
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
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.
Beginning with Version 9.2 the adjacent key locking problem (well known in version 7) was eleminated. Starting with version 9.4 the behaviour of the btree scanners (former btree cleaners) was changed. This all reduces the number of locks posed in the index and helps reducing the lock timeout. The advantag of separated index from data can alredy be used in version 7. You only have to create the index explicit in an separate dbspace (that can be the same dbspaces as the table resides). gerd > -----Ursprüngliche Nachricht----- > Von: ids@iiug.org > Gesendet: 18.05.06 13:07:40 > An: ids@iiug.org > Betreff: locking and index [6752] > > 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. > _______________________________________________________________ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192