The Return Statement is written incorrectly, causing the database table to be locked for a long time.

When a friend in the project is using the transaction, return is written in the wrong place. The transaction is not over yet, and SP is over. Then we use select * to aggregate all data, only select * Form Table1 with (nolock) can be used. However, I also found a good storage process for rewriting sp_who_lock on the Internet and shared it with everyone.

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

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)
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 in the temporary table
Select @ intcountproperties = count (*), @ intcounter = 1
From # tmp_lock_who

If @ error <> 0 return @ Error

If @ intcountproperties = 0
Select 'no blocking and deadlock information' as message

-- Start of Loop
While @ intcounter <= @ intcountproperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ BL = bl
From # tmp_lock_who where id = @ intcounter
Begin
If @ spid = 0
Select '+ Cast (@ BL as varchar (10) +' causes database deadlock. The SQL statement executed by 'process No. 'is as follows'
Else
Select 'process No. spid: '+ Cast (@ spid as varchar (10) +' quilt '+' process no. spid: '+ Cast (@ BL as varchar (10 )) + 'blocking, the SQL syntax executed by the current process is as follows'
DBCC inputbuffer (@ BL)
End

-- Move the loop pointer down
Set @ intcounter = @ intcounter + 1
End

Drop table # tmp_lock_who

Return 0
End