Sql-server – SP is locking due to Begin Transaction

sql servert-sql

Please read the following code

SET IMPLICIT_TRANSACTIONS ON

BEGIN TRANSACTION 
DELETE FROM xxxx
SELECT * FROM xxxxxxxx
WHERE EXISTS (  SELECT * FROM xxxxx
                WHERE CONCAT(xxxxxxx) =  CONCAT(xxxxxxx)
            )

COMMIT

After this I run the following SP

CREATE PROCEDURE [dbo].[xxxxxx]
AS
BEGIN
---To remove any duplicates
WITH CTE AS(
   SELECT xxxxxx, xxxxxx, xxxx,
       RN = ROW_NUMBER()OVER(PARTITION BY xxxxx, xxxx ORDER BY xxxx)
   FROM xxxxxxx
)

delete FROM CTE WHERE RN > 1

---Insert and load
insert into xxxxxxxx
select xxxxxxx.* 
from xxxxxxx
left join xxxxx
on 
    xxxxxxxxxxx=xxxxxxxx COLLATE SQL_Latin1_General_CP1_CI_AI


where xxxxx is null

truncate table xxxxx_Staging


END

The above SP stuck and continue to run due to lock. Why my SP is stuck even I am using Being Transaction before Delete in the first staement. I dont want to use with(NOlock) option in the SP. Anyway to write SP in a better way.

Best Answer

You turned on IMPLICIT_TRANSACTION mode, which will start a new transaction on any statement that writes to the database. And that transaction must be committed or rolled back explicitly, even if the transaction is started inside a stored procedure.

EG

use tempdb
go
drop table if exists t
create table t(id int)
go

create or alter procedure foo
as
begin
  insert into t(id) values (1)
end
go
SET IMPLICIT_TRANSACTIONS ON
exec foo

select @@trancount

outputs

1
Related Question