lck_m_x Lock Locks Whole Database

Asked Jan 28 '15 at 19:48

Active Jan 28 '15 at 19:48

Viewed 827 times

We're running into a situation where the whole database becomes inaccessible. In looking at Activity Monitor and sorting by duration, I see a tons of backlogged queries, the following being oldest query

(@0 nvarchar(max) ,@1 nvarchar(max) ,@2 decimal(4,2),@3 decimal(1,0),@4 datetime,@5 int,@6 int,@7 int)UPDATE [model].[InsurancePolicies]
SET [PolicyCode] = @0, [GroupCode] = @1, [Copay] = @2, [Deductible] = @3, [StartDateTime] = @4, [PolicyholderPatientId] = @5, [InsurerId] = @6, [MedicareSecondaryReasonCodeId] = NULL
WHERE ([Id] = @7)

producing a lck_m_x Lock.

It just sits there forever without making progress. What I can't discern is

Why is a simple update by Id taking so long?
Why is it producing an lck_m_x lock and what does that mean?
Why does that one query lock the entire database?

If I kill that one process in Activity Monitor, all the subsequent queries (SELECTS, UPDATES, et al) (which affect a variety of unrelated tables) all clear almost immediately.

Any suggestions to resolve?

asked Jan 28 '15 at 19:48

Jeff

35,755
15
108
220

Did you check sys.dm_exec_requests to see what this query is waiting on? Look at blocking_session_id, wait_type, last_wait_type, etc. – Aaron Bertrand Jan 28 '15 at 19:53
If it wasn't the cause though, killing that processes wouldn't resolve the issue and allow all subsequent processes through, would it? – Jeff Jan 28 '15 at 19:56
Sorry, I stopped at the end of the bullet list. My point still stands: we can't guess why that one process was slow/stuck. You'll need to investigate further when it happens again. – Aaron Bertrand Jan 28 '15 at 19:58
Okay, it's happening right now...what do I look for in sys.dm_exec_requests? – Jeff Jan 28 '15 at 20:16
See my first comment – Aaron Bertrand Jan 28 '15 at 20:17
Why are you updating a table in the model database? – Namphibian Jan 28 '15 at 21:06
It's not the model database. It's a schema named model – Jeff Jan 28 '15 at 21:33

lck_m_x Lock Locks Whole Database

0 Answers0