SQLSERVER 死锁

select
request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from
sys.dm_tran_locks
where
resource_type=’OBJECT’

;with tran_locks as(
select resource_type,db_name(resource_database_id) as
db_name,resource_description
   ,object_name(resource_associated_entity_id,resource_database_id)
as
object_name,request_mode,request_type,request_status,request_session_id
   from sys.dm_tran_locks
   where resource_type=’OBJECT’
union
select resource_type,db_name(resource_database_id) as
db_name,resource_description
   ,object_name(p.object_id,l.resource_database_id) as
object_name,request_mode,request_type,request_status,request_session_id
   from sys.dm_tran_locks as l
   join sys.partitions as p on
l.resource_associated_entity_id=p.hobt_id
   where resource_type in (‘PAGE’,’KEY’)
union
select resource_type,db_name(resource_database_id) as
db_name,resource_description
   ,null as
object_name,request_mode,request_type,request_status,request_session_id
   from sys.dm_tran_locks as l
   where resource_type not in (‘OBJECT’,’PAGE’,’KEY’)
)select l.*,s.login_name,text from tran_locks as l
    join sys.dm_exec_sessions as s on
s.session_id=l.request_session_id
    left join sys.dm_exec_requests as r on
r.session_id=s.session_id
    cross apply sys.dm_exec_sql_text(sql_handle)
    where not (resource_type=’DATABASE’ and request_mode=’S’ and
request_type=’LOCK’ and request_status=’GRANT’)
    –and db_name=”
    –and object_name=”
    order by db_name,object_name,request_type

 

然后kill 里面的进程