?
Solved

SPID blocking lock wait LCK_M_U LCK_M_IX

Posted on 2016-08-06
9
Medium Priority
?
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

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 35

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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

719 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