Solved

SPID blocking lock wait LCK_M_U LCK_M_IX

Posted on 2016-08-06
9
69 Views
Last Modified: 2016-08-12
Hello

two SPID wait LCK_M_U since 16 hours.
I have incerase max server memory but the session are suspended.
What's happened if I executed :

dbcc freesystem cache
dbcc freesession cache
dbcc freeproccache

Thanks
0
Comment
Question by:bibi92
  • 4
  • 3
  • 2
9 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 41745460
It's an update lock. So these two procedures seems to update the same data. Either you tune your queries, e.g. creating some appropriate indices or you may consider using a different isolation level.
0
 

Author Comment

by:bibi92
ID: 41745512
ok but I can't kill SPID and  create index
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41745524
The problem is that SQL Server seems not to see this is dead-lock. So you need to identify the applications running these transactions and quit them.

Without killing them or killing the SPID, you need to wait further. But I don't think this is an option.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 41745612
Those locks are waiting for the resource to become available and held in a wait state. So, those spids aren't the ones you are looking for (sounds like a line from star wars).

You need to find (and KILL if safe to do so) the blocking ID, or restart SQL Server if the reasons for the lock cannot be found. The restart is of course the last resort.

It is unlikely the IS because that is an INTENT SHARED lock, but more the UPDATE lock and you need to see what it is waiting on (use SQL server to show you), then go find what is blocking (or holding that resource in a lock).

Pinal Dave wrote a couple of good posts to help explain : http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/ and to help resolve : http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

And you should follow up with ste5an suggestions and also look into the blocked process threshold : https://msdn.microsoft.com/en-us/library/ms191168.aspx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:bibi92
ID: 41745630
Hello,

I have already found the query but I can't kill the transaction. Thanks
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 41745656
What do you mean by "can't kill" ?

Can you see any results from the query ? It will be the blocking ID that you want to focus on.

Or, maybe you have external / remote processes in play. Like SSIS or outputting to a locked spread sheet (or a number of other possibilities, linked servers, replication, etc)
0
 

Author Comment

by:bibi92
ID: 41745736
I can't kill the SPID because update transaction. No update is executed on stored procedure.
I see that the sessions is suspended : read 0, write 0, physical_reads 0, used_memory 1188, open_tran_count.

Thanks
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 41745804
OK, but did the query :

select  db.name DBName,
		tl.request_session_id,
		wt.blocking_session_id,
		OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
		tl.resource_type,
		h1.TEXT AS RequestingText,
		h2.TEXT AS BlockingTest,
		tl.request_mode
FROM sys.dm_tran_locks AS tl
inner JOIN sys.databases db ON db.database_id = tl.resource_database_id
inner JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
inner JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
inner JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
inner JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
 GO

Open in new window


return anything ?

The request_session_id should be the one being blocked by the blocking_session_id and the blocking one is the one you need to inspect.

What query are you running ? Please confirm how you are identifying the SPID and seeing the update being locked. Are you getting any error messages ?

You can also see the blocking_id from :
select * from sys.dm_os_waiting_tasks

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 41754140
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now