RE: Shared locks being held for arbitrarily long time

How do you set lock mode to not wait? Thanx. -----Original Message----- From: McCabe-Reed, Barbara SIK Sent: Friday, April 16, 2004 2:41 PM To: 'andykent.bristol1095@virgin.net'; informix-list@iiug.org Subject: RE: Shared locks being held for arbitrarily long time It has been a long time since I worked on the Informix engine. How do you set the lock mode to not wait? Thanx. -----Original Message----- From: andykent.bristol1095@virgin.net [mailto:andykent.bristol1095@virgin.net] Sent: Friday, April 16, 2004 10:20 AM To: informix-list@iiug.org Subject: Re: Shared locks being held for arbitrarily long time Query sysmaster:syslocks to see if it's waiting on a lock? Keep an eye on onstat -u|grep <tid>? Whatever's holding the lock it wants is holding a transaction open too long? Maybe because its SQL needs tuning? Try setting LOCK MODE to NOT WAIT and see if it barfs? Run SET EXPLAIN on it to see if it's reading more data than it needs to? Try running it with an isolation level of DIRTY READ? Slow checkpoint? Waiting for logs to free up? Just my twopennorth. Andy miellian@hotmail.com (Ian) wrote in message news:<295ea884.0404130423.6f3d397e@posting.google.com>... > We have a problem with a stored procedure we are calling within a > transaction. > > Under load (but erratically) the individual transactions seem to stop. > Running a query to examine the locks held at the time reveals that > when this occurs each session has exactly the same profile. Each > session has the expected exclusive locks for inserts and updates, but > the stopping point is always where some shared locks are held. > > Normally one would expect that this is due to an inefficient query. > Looking at the corresponding select query, the shared locks are > consistent with only some of the tables in the query. Further, the > query is "--+ ORDERED", and the first table in the from clause does > not have a shared lock on it. It is as if the query planner/scheduler > has jammed and isn't able to complete/begin the query proper. > > However, the session and transaction always completes successfully > after up to 90 seconds of waiting (our lock mode wait is always = 20). > As I say, this does not seem directly related to load, and doesn't > match the behaviour of locking/deadlock problems we've seen in the > past. The code has also been checked by hand. Does anyone know why > this might be? > > -ian miell sending to informix-list sending to informix-list

McCabe-Reed, Barbara SIK wrote: > How do you set lock mode to not wait? Thanx. RTF(abulous)M under SET LOCK MODE TO NOT WAIT. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/