Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

SPID blocking lock wait LCK_M_U LCK_M_IX

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
bibi92
Asked:
bibi92
  • 4
  • 3
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
bibi92Author Commented:
ok but I can't kill SPID and  create index
0
 
ste5anSenior DeveloperCommented:
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
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!

 
Mark WillsTopic AdvisorCommented:
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
 
bibi92Author Commented:
Hello,

I have already found the query but I can't kill the transaction. Thanks
0
 
Mark WillsTopic AdvisorCommented:
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
 
bibi92Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
bibi92Author Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now