Using system view: sys.sysprocesses to check SqlServer's block and deadlock

You also can read the Chinese version: 秋天的林子---SQLSERVER的阻塞和死锁

 

Sys.SysProcesses: it's a important system view, it can locate and resolve block and deadlock.

Some fields（it from MSDN）：

 

spid	smallint	SQL Server session ID.
kpid	smallint	Windows thread ID.
blocked	smallint	ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is owned by a deferred recovery transaction. -4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
waittype	binary(2)	Reserved.
waittime	bigint	Current wait time in milliseconds. 0 = Process is not waiting.
lastwaittype	nchar(32)	A string indicating the name of the last or current wait type.
waitresource	nchar(256)	Textual representation of a lock resource.
dbid	smallint	ID of the database currently being used by the process.
uid	smallint	ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767.
cpu	int	Cumulative CPU time for the process. The entry is updated for all processes, regardless of whether the SET STATISTICS TIME option is ON or OFF.
physical_io	bigint	Cumulative disk reads and writes for the process.
memusage	int	Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
login_time	datetime	Time at which a client process logged into the server.
last_batch	datetime	Last time a client process executed a remote stored procedure call or an EXECUTE statement.
ecid	smallint	Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
open_tran	smallint	Number of open transactions for the process.
status	nchar(30)	Process ID status. The possible values are: dormant = SQL Server is resetting the session. running = The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS). background = The session is running a background task, such as deadlock detection. rollback = The session has a transaction rollback in process. pending = The session is waiting for a worker thread to become available. runnable = The task in the session is in the runnable queue of a scheduler while waiting to get a time quantum. spinloop = The task in the session is waiting for a spinlock to become free. suspended = The session is waiting for an event, such as I/O, to complete.
sid	binary(86)	Globally unique identifier (GUID) for the user.
hostname	nchar(128)	Name of the workstation.
program_name	nchar(128)	Name of the application program.
hostprocess	nchar(10)	Workstation process ID number.
cmd	nchar(16)	Command currently being executed.
nt_domain	nchar(128)	Windows domain for the client, if using Windows Authentication, or a trusted connection.
nt_username	nchar(128)	Windows user name for the process, if using Windows Authentication, or a trusted connection.
net_address	nchar(12)	Assigned unique identifier for the network adapter on the workstation of each user. When a user logs in, this identifier is inserted in the net_address column.
net_library	nchar(12)	Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that enables them to make the connection.
loginame	nchar(128)	Login name.
context_info	binary(128)	Data stored in a batch by using the SET CONTEXT_INFO statement.
sql_handle	binary(20)	Represents the currently executing batch or object. Note This value is derived from the batch or memory address of the object. This value is not calculated by using the SQL Server hash-based algorithm.
stmt_start	int	Starting offset of the current SQL statement for the specified sql_handle.
stmt_end	int	Ending offset of the current SQL statement for the specified sql_handle. -1 = Current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.
request_id	int	ID of request. Used to identify requests running in a specific session.
page_resource	binary(8)	Applies to: SQL Server 2019 preview  An 8-byte hexadecimal representation of the page resource if the waitresource column contains a page.

 

example of application：

1. Check DB if have blocked

first to find which process's blocked filed is not 0. example: if rSPID53's blocked filed is not 0, it equal 52 and SPID's blockd is 0, you can get the conclusion now: blocked occurs, the process 53 is blocked by 52. in another situation, if you found that the blocked filed same as itself, it means the process is reading and writing disk.

2. Which DB is the process

only check dbid, using the following query

Select name,dbid from master.sys.sysdatabases

3. check the process corresponding SQL code

dbcc inputbuffer(spid);
4. KILL the process

kill spid

5. sql blocked and process query

select A.SPID as 'the process is blocked', a.CMD AS 'execute type',b.spid AS 'blocked process',b.cmd 
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
DBCC INPUTBUFFER(59   )

exec sp_who 'active'--all of system process and the process is blocked if BLK field is not 0
exec sp_lock SPID --Returns a process lock on the resource situation
SELECT object_name(1093578934)--Returns object ID corresponding object name 
DBCC INPUTBUFFER (63)--show the SPID's statement

6. SQL Server is running  sql statements

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
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];

 

SqlServer query deadlock process

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

 

as to sqlserver check deadlock kill lock

use master

go

create procedure sp_who_lock

as

begin

declare @spid int,@bl int,

@intTransactionCountOnEntry int,

@intRowcount int,

@intCountProperties int,

@intCounter int

create table #tmp_lock_who (

id int identity(1,1),

spid smallint,

bl smallint)

IF @@ERROR<>0 RETURN @@ERROR

insert into #tmp_lock_who(spid,bl) select 0 ,blocked

from (select * from sysprocesses where blocked>0 ) a

where not exists(select * from (select * from sysprocesses where blocked>0 ) b

where a.blocked=spid)

union select spid,blocked from sysprocesses where blocked>0

IF @@ERROR<>0 RETURN @@ERROR


select @intCountProperties = Count(*),@intCounter = 1

from #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

if @intCountProperties=0

select 'no block and deadlock now' as message

while @intCounter <= @intCountProperties

begin

select @spid = spid,@bl = bl

from #tmp_lock_who where Id = @intCounter

begin

if @spid =0

select 'DB deadlock is caused by : '+ CAST(@bl AS VARCHAR(10)) + 'its statement is:'

else

select 'SPID：'+ CAST(@spid AS VARCHAR(10))+ ' is blocked by ' + 'SPID：'+ CAST(@bl AS VARCHAR(10)) +',its statement is:'

DBCC INPUTBUFFER (@bl )

end

set @intCounter = @intCounter + 1

end

drop table #tmp_lock_who

return 0

end

 

--kill lock and process

use master

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_killspid]

GO

create proc p_killspid

@dbname varchar(200) 

as

declare @sql nvarchar(500)

declare @spid nvarchar(20)

declare #tb cursor for

select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)

open #tb

fetch next from #tb into @spid

while @@fetch_status=0

begin

exec('kill '+@spid)

fetch next from #tb into @spid

end

close #tb

deallocate #tb

go


--exec p_killspid 'newdbpy'

 

--view some lock information

create table #t(req_spid int,obj_name sysname)

declare @s nvarchar(4000)

,@rid int,@dbname sysname,@id int,@objname sysname

declare tb cursor for

select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid

from master..syslockinfo where rsc_type in(4,5)

open tb

fetch next from tb into @rid,@dbname,@id

while @@fetch_status=0

begin

set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'

exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id

insert into #t values(@rid,@objname)

fetch next from tb into @rid,@dbname,@id

end

close tb

deallocate tb

select 'process id'=a.req_spid

,DB=db_name(rsc_dbid)

,'Type'=case rsc_type when 1 then 'NULL （no use）'

when 2 then 'DB'

when 3 then 'file'

when 4 then 'Index'

when 5 then 'table'

when 6 then 'page'

when 7 then 'key'

when 8 then 'expand the disk'

when 9 then 'RID（row ID)'

when 10 then 'application'

end

,rsc_objid

,b.obj_name

,rsc_indid

from master..syslockinfo a left join #t b on a.req_spid=b.req_spid

go

drop table #t

--view no commit transaction

USE master

GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id, es.login_name, es.host_name, est.text

, cn.last_read, cn.last_write, es.program_name

FROM sys.dm_exec_sessions es

INNER JOIN sys.dm_tran_session_transactions st  

ON es.session_id = st.session_id

INNER JOIN sys.dm_exec_connections cn

ON es.session_id = cn.session_id

CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est

LEFT OUTER JOIN sys.dm_exec_requests er

ON st.session_id = er.session_id

AND er.session_id IS NULL

 

view which tables is locked

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName  

from   sys.dm_tran_locks where resource_type='OBJECT'