Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SPID blocking lock wait LCK_M_U LCK_M_IX

Posted on 2016-08-06
9
Medium Priority
?
409 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 36

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 36

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 

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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

972 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