LCK_M_SCH_S, the schema stability lock caused my stored proc to waiting forever. Is there any way to avoid or got over it?
Asked
Active
Viewed 1.2k times
-3
-
1procedure definition ????? – M.Ali Mar 28 '15 at 18:48
-
It's a proc with dynamic query, which uses sys.objects,sys.collumns and sys.types to get object information from any database on the sql server. – Bambam Deo Mar 28 '15 at 18:54
-
1Can you post the query or part of the query? – Alex Mar 28 '15 at 19:22
-
What is the lock type and resource of the blocker? – Dan Guzman Mar 28 '15 at 21:14
2 Answers
2
LCK_M_SCH_S is a schema stability lock. This lock is used when queries are being compiled and executed. Lengthy blocking by this type of lock suggest you have a long running query in one or more of your databases. Other than batches these long running queries there is no easy way to get around this type of lock.
Alex
- 21,273
- 10
- 61
- 73
-
No, just accessing master db to get object information from any database on the sql server. – Bambam Deo Mar 28 '15 at 18:56
-
@jacodegroot, LCK_M_SCH_S is a schema stability lock. A schema modification lock is LCK_M_SCH_M. – Dan Guzman Mar 28 '15 at 21:11
-
After Dan Guzman's comment I have changed my answer to reflect this is a schema stability lock and not a schema modifcation lock – Alex Mar 28 '15 at 21:30
1
Sorry , My Question was not Clear, But I've got the Solution, inspired by Jaco de Groot's Answer This lock was due to another Stored proc of masterdb. sp_helpconstraint ,used to find constraints on tables in current database. I used this in my Proc, to get Constraints Details on tables/views I was applying this on View, which caused LCK_M_SCH_S, as I found. Before applying this Proc, I tested for the object(whether type is 'U' or not), It works fine.
Bambam Deo
- 148
- 1
- 4
- 15
-
1You should accept this answer to your question to show it close. – Mark Schultheiss Jun 18 '18 at 14:27