SQL SERVER 死锁

 sp_lock

查看锁表名称

 查看进程信息

select *from FROM sys.sysprocesses --全部进程

select *from FROM sys.sysprocesses where sp=xxx--指定进程

sp_who active --看看哪个引起的死锁, blk里面即阻塞的spid；

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

spid 锁表进程
tableName 被锁表名

解锁：
kill  spid

 

DBCC INPUTBUFFER(spid)  --查看改进程的sql 语句

 

blocked为0的是引起死锁的，blocked不为0的是被等待的进程，等待释放资源的才继续执行。等待资源。waitResource

 

 

SELECT
    spid,
    blocked,
    DB_NAME(sp.dbid) AS DBName,
    program_name,
    waitresource,
    lastwaittype,
    sp.loginame,
    sp.hostname,
    a.[Text] AS [TextData],
    SUBSTRING (
        A. TEXT,
        sp.stmt_start / 2,
        (
            CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start
        ) / 2
    ) AS [current_cmd] 
    FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A 
    WHERE spid > 50 and  spid in(  select request_session_id spid 
from sys.dm_tran_locks where resource_type='OBJECT')
    ORDER BY blocked DESC,
    DB_NAME(sp.dbid) ASC,
    a.[text]

--查看锁抢夺资源
sp_lock 

 

例如A进程（锁住资源未提交未完成）

begin tran
update c_FeeType  set DM=dm

B进程（等待）

select * from c_FeeType

 

SELECT * FROM SYS.partitions WHERE hobt_id=72057594050445312

SELECT * FROM r_register where %%lockres%% = '(a791659675d9)'

 

 

fdquery selct*from  没有FetcheALL，其他进程update时抢不到资源，锁住了。