identify the user who lock the table or index

Hi, i have face error "245: Could not position within a file via an index.144: ISAM error: key value locked" in informix 11.70 time to time so it's takingtime find exact locked user so can i write a some script to get locked user when it got locked or is there any ezy way to find out this. Thanks pushpa

Hello. If you are trying to make some administrative operation, that may require some lock on that table, you could set IFX_DIRTY_WAIT, in your command line, before executing the script/app. If this issue is happening in your regular user/app session, you might check your engine, and may require some tunning to avoid lock, reducing lock contention level. There is an excellent article (old one, but still the same for today), that might help you. http://www.ibm.com/developerworks/data/library/techarticle/dm-0609herber/ Also check the Information Center website for "transaction isolation level". That are some levels that can reduce your lock contentions, but will depend on your Informix version (IE, GE, etc)... If you still have doubts, you could specify in details, what is exactly you´re issue with that sql error, and we could help you further. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: pushpa@cybersoft.lk > Subject: identify the user who lock the table or index [30817] > Date: Thu, 11 Jul 2013 07:53:53 -0400 > > Hi, > > i have face error "245: Could not position within a file via an index.144: > ISAM error: key value locked" in informix 11.70 time to time so it's taking> time find exact locked user so can i write a some script to get locked user > when it got locked or is there any ezy way to find out this. > > Thanks > pushpa > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Ow... there is a technote regarding exactly what you asked for.... just in case.... http://www-01.ibm.com/support/docview.wss?uid=swg21226344 Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant From: alexandre@briug.org To: ids@iiug.org Subject: RE: identify the user who lock the table or index [30817] Date: Thu, 11 Jul 2013 09:07:55 -0300 Hello. If you are trying to make some administrative operation, that may require some lock on that table, you could set IFX_DIRTY_WAIT, in your command line, before executing the script/app. If this issue is happening in your regular user/app session, you might check your engine, and may require some tunning to avoid lock, reducing lock contention level. There is an excellent article (old one, but still the same for today), that might help you. http://www.ibm.com/developerworks/data/library/techarticle/dm-0609herber/ Also check the Information Center website for "transaction isolation level". That are some levels that can reduce your lock contentions, but will depend on your Informix version (IE, GE, etc)... If you still have doubts, you could specify in details, what is exactly you´re issue with that sql error, and we could help you further. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: pushpa@cybersoft.lk > Subject: identify the user who lock the table or index [30817] > Date: Thu, 11 Jul 2013 07:53:53 -0400 > > Hi, > > i have face error "245: Could not position within a file via an index.144: > ISAM error: key value locked" in informix 11.70 time to time so it's taking> time find exact locked user so can i write a some script to get locked user > when it got locked or is there any ezy way to find out this. > > Thanks > pushpa > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

List all locks in the database for a specific table: database sysmaster; set isolation to dirty read; select type ,owner ,a.username[1,8] lock_owner ,uid ,pid ,hostname ,tty from syslocks, syssessions a where owner = a.sid and tabname = "xxx" and dbsname = "yyyy" Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "PUSHPA KUMARA" <pushpa@cybersoft.lk> An: ids@iiug.org Gesendet: Donnerstag, 11. Juli 2013 13:53:53 Betreff: identify the user who lock the table or index [30817] Hi, i have face error "245: Could not position within a file via an index.144: ISAM error: key value locked" in informix 11.70 time to time so it's takingtime find exact locked user so can i write a some script to get locked user when it got locked or is there any ezy way to find out this. Thanks pushpa ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.