Follow

Waits starting with LCK

Introduction

The SQL Server has a lot of wait types starting with LCK. In a SQL Server 2014 there are 63 of them, and you will see them in many variations like

  1. LCK_M_S
  2. LCK_M_SCH_M
  3. LCK_M_X

and many more. At the bottom of this article, you can see a complete list of LCK_xxx wait types in Microsoft SQL server 2014, just to give you an impression about how many there are.

So basically LCK means LOCKS, and as a default Microsoft SQL Server uses locks to make sure data are correct.

So when you see a high number of LCK waits, then you might have concurrency problems.

What to do

There are many things that can be done to investigate and/or solve the problem. We would recommend to do the following:

  1. Identify what statements that are blocking others.
  2. See if these statements or transactions can be rewritten.
  3. See if you can change isolation level. But that has consequences if you do it in a wrong way. You must understand isolation levels before you do anything.

I know that there is a lot of difference in the different wait types, but that is basically the idea with LCK wait types.

Regarding Identifying, then one idea could be to use the dynamic management view sys.dm_tran_locks.

Complete list of Lock related wait types

LCK_M_SCH_S
LCK_M_SCH_M
LCK_M_S
LCK_M_U
LCK_M_X
LCK_M_IS
LCK_M_IU
LCK_M_IX
LCK_M_SIU
LCK_M_SIX
LCK_M_UIX
LCK_M_BU
LCK_M_RS_S
LCK_M_RS_U
LCK_M_RIn_NL
LCK_M_RIn_S
LCK_M_RIn_U
LCK_M_RIn_X
LCK_M_RX_S
LCK_M_RX_U
LCK_M_RX_X
LCK_M_SCH_S_LOW_PRIORITY
LCK_M_SCH_M_LOW_PRIORITY
LCK_M_S_LOW_PRIORITY
LCK_M_U_LOW_PRIORITY
LCK_M_X_LOW_PRIORITY
LCK_M_IS_LOW_PRIORITY
LCK_M_IU_LOW_PRIORITY
LCK_M_IX_LOW_PRIORITY
LCK_M_SIU_LOW_PRIORITY
LCK_M_SIX_LOW_PRIORITY
LCK_M_UIX_LOW_PRIORITY
LCK_M_BU_LOW_PRIORITY
LCK_M_RS_S_LOW_PRIORITY
LCK_M_RS_U_LOW_PRIORITY
LCK_M_RIn_NL_LOW_PRIORITY
LCK_M_RIn_S_LOW_PRIORITY
LCK_M_RIn_U_LOW_PRIORITY
LCK_M_RIn_X_LOW_PRIORITY
LCK_M_RX_S_LOW_PRIORITY
LCK_M_RX_U_LOW_PRIORITY
LCK_M_RX_X_LOW_PRIORITY
LCK_M_SCH_S_ABORT_BLOCKERS
LCK_M_SCH_M_ABORT_BLOCKERS
LCK_M_S_ABORT_BLOCKERS
LCK_M_U_ABORT_BLOCKERS
LCK_M_X_ABORT_BLOCKERS
LCK_M_IS_ABORT_BLOCKERS
LCK_M_IU_ABORT_BLOCKERS
LCK_M_IX_ABORT_BLOCKERS
LCK_M_SIU_ABORT_BLOCKERS
LCK_M_SIX_ABORT_BLOCKERS
LCK_M_UIX_ABORT_BLOCKERS
LCK_M_BU_ABORT_BLOCKERS
LCK_M_RS_S_ABORT_BLOCKERS
LCK_M_RS_U_ABORT_BLOCKERS
LCK_M_RIn_NL_ABORT_BLOCKERS
LCK_M_RIn_S_ABORT_BLOCKERS
LCK_M_RIn_U_ABORT_BLOCKERS
LCK_M_RIn_X_ABORT_BLOCKERS
LCK_M_RX_S_ABORT_BLOCKERS
LCK_M_RX_U_ABORT_BLOCKERS
LCK_M_RX_X_ABORT_BLOCKERS

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.