SELECT Query with READ taking long time in locking (MLK) step

Database
Fan

SELECT Query with READ taking long time in locking (MLK) step

We have a query with CPU less than 20 and for a particular day it was stuck in MLK step for 20 minutes. It tries to put a read lock on around 10 different tables.

 

Later runs have executed within a second. However for 1 day it was stuck in the MLK step for about 20 minutes. The DBQL does not show any writes on any of those tables during that time. Can use some help trying to figure out what was the issue for that particular day.

5 REPLIES
Junior Contributor

Re: SELECT Query with READ taking long time in locking (MLK) step

Maybe there was a not yet commited Begin Transaction followed by a RowHash-lock INSERT/UPDATE/DELETE?

 

Do a Rewind in Viewpoint and check which session caused the block.

Fan

Re: SELECT Query with READ taking long time in locking (MLK) step

Viewpoint shows the session as ACTIVE

Junior Contributor

Re: SELECT Query with READ taking long time in locking (MLK) step

Strange.

Previous "pseudo-lock" steps were running successfully?

Any concurrent backup? 

 

Senior Apprentice

Re: SELECT Query with READ taking long time in locking (MLK) step

As well as a concurrent backup, were there any load utilities running against one/more of the tables involved (Fastload, Multiload etc.) ?

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: SELECT Query with READ taking long time in locking (MLK) step

Thanks for taking the time to reply guys. It seems that the System was heavily degraded for that period which might have caused the issue. Unfortuately the queries we were looking at got stuck in the MLK step. Yes  the previous "pseudo-lock" steps ran successfully