Shared locks being held for arbitrarily long time
Posted in 2004
Topics: Performance & Tuning, Stored Procedures & SPL, Transactions, Locking & Isolation
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