/ * - Handle the deadlock to see the current process, or deadlock process, and automatically kill the death process because it is dead, so if there is a deadlock process, you can only view the deadlock process. Of course, you can control, No matter if there is any dead lock, only the deadlock process is grateful: CAIYUNXIA, JIANGOPEN two reference information - Zou Jian 2004.4- * /
/ * - Call example EXEC P_LOCKINFO - * /
Create
PROC
P_lockinfo @ Kill_lock_spid
Bit
=
1
,
-
Whether to kill the deadlock process, 1 kill, 0 only
@Show_SPID_IF_NOLOCK
Bit
=
1
-
If there is no deadlock process, do you display a normal process information, 1 display, 0 does not display
AS
set
Nocount
on
Declare
@count
int
, @ s
nvarchar
(
1000
), @ i
int
SELECT
id
=
Identity
(
int
,
1
,
1
), Logo, process ID
=
SPID, thread ID
=
KPID, block process ID
=
Blocked, database ID
=
DBID, database name
=
DB_NAME
(DBID), user ID
=
UID, username
=
Loginame, accumulated CPU time
=
CPU, login time
=
Login_time, open the number of transactions
=
Open_TRAN, process status
=
Status, work station name
=
Hostname, application name
=
Program_name, workstation process ID
=
HostProcess, domain name
=
NT_DOMAIN, NIC address
=
NET_ADDRESS
INTO
#T #T #T
From
(
SELECT
Sign
=
'
Deadlock process
'
, SPID, KPID, A. Blocked, DBID, UID, Loginame, CPU, Login_Time, Open_TRAN, STATUS, HOSTNAME, PROGRAM_NAME, HOSTPROCESS, NT_DOMAIN, NET_ADDRESS, S1
=
A.SPID, S2
=
0
From
Master..xysprocesses a
Join
(
SELECT
Blocked
From
Master..xysprocesses
group
BY
Blocked) B
on
a.spid
=
B.Blocked
WHERE
a.blocked
=
0
union
all
SELECT
'
| _ Sacrifice _>
'
, SPID, KPID, Blocked, DBID, UID, Loginame, CPU, Login_Time, Open_TRAN, STATUS, HOSTNAME, Program_name, HostProcess, NT_DOMAIN, NET_ADDRESS, S1
=
Blocked, S2
=
1
From
Master..xysprocesses a
WHERE
Blocked
<>
0
) a
ORDER
BY
S1, S2
SELECT
@count
=
@@ rowcount, @ i
=
1
IF
@count
=
0
and
@Show_SPID_IF_NOLOCK
=
1
Begin
insert
#T #T #T
SELECT
Sign
=
'
Normal process
'
, SPID, KPID, Blocked, DBID,
DB_NAME
(DBID), UID, Loginame, CPU, Login_Time, Open_TRAN, STATUS, HOSTNAME, Program_Name, HostProcess, NT_DOMAIN, NET_ADDRESS
From
Master..xysprocesses
set
@count
=
@@ rowcount
end
IF
@count
>
0
Begin
Create
TABLE
# T1 (id)
int
Identity
(
1
,
1
), a
nvarchar
(
30
), B
Int
EventInfo
nvarchar
(
255
))
IF
@kill_lock_spid
=
1
Begin
Declare
@SPID
VARCHAR
(
10
), @ Mark
VARCHAR
(
10
)
While
@i
<=
@count
Begin
SELECT
@SPID
=
Process ID, @ logo
=
Sign
From
#T #T #T
WHERE
id
=
@i
insert
# T1
EXEC
(
'
DBCC INPUTBUFFER
'
@SPID
'
)
'
)
IF
@@ rowcount
=
0
insert
# T1 (a)
Values
(
NULL
)
IF
@ 标志
=
'
Deadlock process
'
EXEC
(
'
Kill
'
@SPID)
set
@i
=
@i
1
end
end
Else
While
@i
<=
@count
Begin
SELECT
@S
=
'
DBCC INPUTBUFFER
'
CAST
(Process ID
AS
VARCHAR
)
'
)
'
From
#T #T #T
WHERE
id
=
@i
insert
# T1
EXEC
(@S)
IF
@@ rowcount
=
0
insert
# T1 (a)
Values
(
NULL
)
set
@i
=
@i
1
end
SELECT
a.
*
SQL statement for the process
=
B. EventInfo
From
#t a
Join
# T1 B
on
A.ID
=
B.ID
ORDER
BY
Process ID
end
set
Nocount
Off
Go
IF
exists
(
SELECT
*
From
DBO.SYSOBJECTS
WHERE
id
=
Object_id
(N
'
[dbo]. [sp_who_lock]
'
)
and
ObjectProperty
(ID, N
'
ISPROCEDURE
'
)
=
1
)
Drop
Procedure
[
DBO
]
.
[
SP_WHO_LOCK
]
Go
/ ************************************************** ************************** // creation: fengyu mail: Maggiefengyu@tom.com Date: 2004-04-30 // Modification: from Http://www.9cbs.net/develop/read_article.asp?id=26566 Learn and rewrite // Description: View database blocked and deadlock situation ************** *********************************************************** ********** / 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
-
Find the number of records of the temporary table
SELECT
@intcountproperties
=
Count
(
*
), @ Intcounter
=
1
From
#tmp_lock_who
IF
@@ error
<>
0
Return
@@ error
IF
@intcountproperties
=
0
SELECT
'
There is no blocking and deadlock information now
'
AS
Message
-
Cycle start
While
@intcounter
<=
@intcountproperties
Begin
-
Take the first record
SELECT
@SPID
=
SPID, @ BL
=
BL
From
#tmp_lock_who
WHERE
Id
=
@intcounter
Begin
IF
@SPID
=
0
SELECT
'
The database is deadlock is:
'
CAST
(@BL
AS
VARCHAR
(
10
))
'
The process number, the SQL syntax of its execution is as follows
'
Else
SELECT
'Process number SPID:
'
CAST
(@SPID
AS
VARCHAR
(
10
))
'
Be
'
'
Process number SPID:
'
CAST
(@BL
AS
VARCHAR
(
10
))
'
Block, the SQL syntax executed by its current process is as follows
'
DBCC
InputBuffer (@BL)
end
-
Circulating pointer down
set
@intcounter
=
@intcounter
1
end
Drop
TABLE
#tmp_lock_who
Return
0
end
