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

McCabe-Reed, Barbara SIK wrote: > It has been a long time since I worked on the Informix engine. How do you > set the lock mode to not wait? Thanx. RTF(abulous)Manual? set lock mode to not wait; (Really - you were that close!) > -----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 -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/