Re: Shared locks being held for arbitrarily long time

Thanks for this Andy. Our first thought was some kind of locking problem, but the only locks held on the database server at the time are in the process itself. Lock mode is set to wait for 20 seconds, and it stops for up to 100+ seconds, so we've eliminated that possibility. We've run set the sql with set explain on, and the plan follows the ordering as expected. Checkpoints don't match (and other transactions complete (even ones running the same sp) while this one waits, so it's not some generic problem with the database server). What we've done is take the sql out of the sp line for line and run it in the client application. Bizarrely, it performs better in the best case (ie when all is well) under load than the stored procedure. We suspect there's something fishy with the instance configuration, but since the behaviour is not consistent in any way we can find, figuring out what it is is tricky. -ian andykent.bristol1095@virgin.net (Andy Kent) wrote in message news:<ac5fb36.0404160620.d1d185f@posting.google.com>... > 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