why query shows in suspended task state and LCK_M_X wait type

Question

I am working on winform application using C# 4.0, back end SQL SERVER 2012. Some time, my software seems to be hang, because queries become pending, because one query shows in activity monitor in suspended mode, this suspended query is run successfully several time but some time this problem occurs.

The activity monitor shows :

Task State : Suspended

command : Update

Application : .net SqlClient Data Provider

Wait time : 5673610..... (unlimited)

Wait Type : LCK_M_X

Wait Resource : keylock hobtid=72057594058768384 dbid=14 id=lock2dbc30880 mode=X associatedObjectId=72057594058768384

What is the reason of a query state become suspended.

how can i stop it to be suspended ?

Answer 1

LCK_M_X indicates that a query is waiting to acquire an exclusive lock somewhere. Try checking other applications or threads using the same database objects to see if there's a race condition or a process that isn't cleaning up after itself.

Perhaps there's another db call or nested proc call in your code somewhere?

Try using Sql Profiler to identify what the lock request is coming from - This may help, but its difficult to say without knowing more.

select * from sys.dm_tran_locks

HTH