Solved

SPID blocking lock wait LCK_M_U LCK_M_IX

Posted on 2016-08-06
9
121 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 33

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 33

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

772 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