Table lock is not released after running stored procedure

Rachel 1

I have a stored procedure doing DELETE, INSERT, SELECT on an existing table.
In my application(using a connection pool), after running this stored procedure, the DB connection is still there. When I run this stored procedure one more time, the DELETE statement is blocked, the wait type is LCK_M_U.
Why the 1st connection still holds the table lock after running this stored procedure?
How to make sure the table lock is released?

Shashank Singh 6,251 Reputation points

2020-11-13T06:14:06.193+00:00

Are you calling this SP from application, its quite possible this is not being properly closed from application and you might as well be using explicit transaction so locks are getting held.

4 answers

m 4,271 Reputation points

2020-11-13T07:41:30.02+00:00
Hi @Rachel ,

Why the 1st connection still holds the table lock after running this stored procedure?

Maybe it is not be committed， try code commit tran will sove the issue.

Verify it is blocked by this stored procedure or not:

--CHECK WAITTIME SPID DBID select * from sys.sysprocesses --check resource and objectid sp_lock --check table select * from sys.objects where object_id = ' ' --check t_sql select * from sys.dm_exec_requests as p cross apply sys.dm_exec_sql_text (p.sql_handle) as qt where p.session_id >50

How to make sure the table lock is released?

Firstly find the SPID and then commit it or kill it.

--check open tran DBCC OPENTRAN --kill tran Kill SPID --commit tran Commit tran

More information: inf-understanding-and-resolving-sql-server-blocking-problems
BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Rachel 1 Reputation point

2020-11-13T20:52:05.707+00:00

Thank you for the suggestions!
I added the "BEGIN TRANSACTION/COMMIT" in the stored procedure. From my application, the table lock was still there, until my application Connection Pool manager closed the connection or I kill the connection manually from Management Studio.
Plus, I tested it in a standalone java program, the alive DB connection doesn't hold the table lock after running the stored procedure.
My application is a client-server application, the table lock is not released after running the stored procedure. What's the possible cause? Thank you!
Please sign in to rate this answer.
m 4,271 Reputation points

2020-11-16T03:27:00.187+00:00

Hi @Rachel ,

OK. There is no open tran after your stored procedure is executed finish, I mean is there one open tran out of your stored procedure?
Do you enable one implicit transaction? If not, can you provide us with your test code （include the stored procedure）for testing? Could you please check it on another Server?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Rachel 1 Reputation point

2020-11-18T03:50:54.077+00:00

My application (Java program) is hard to provide. I tested with a simple stored procedure like the following. I just saw a similar question in https://stackoverflow.com/questions/34674039/sql-transaction-doesnt-release-lock-after-commit

"If @@trancount > 0 rollback" did the trick, is this solution safe? Thank you.

BEGIN TRAN

DELETE FROM [retailforecast].[dbo].[arima_forecast2];

insert into arima_forecast2 select * FROM arima_forecast1;

COMMIT TRAN

If @@trancount > 0 rollback

SELECT [time]
,[forecast]
,[lo95]
,[hi95]

FROM [retailforecast].[dbo].[arima_forecast2];
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
m 4,271 Reputation points

2020-11-17T01:40:17.417+00:00

Hi @Rachel ,

Is the issue solved?
Is the reply helpful?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
Rachel 1 Reputation point

2020-11-17T06:15:45.157+00:00

Thank you for your reply!
The issue is still not solved after adding "BEGIN TRANSACTION ..... COMMIT" to the stored procedure.

As long as the connection is open(but the Stored Procedure is complete), Activity Monitor shows its Head Blocker = 1 when running the stored procedure with 2nd connection. 2nd connection's wait type is LCK_M_U.

Shashank Singh 6,251 Reputation points

2020-11-17T06:18:00.287+00:00

The issue is still not solved after adding "BEGIN TRANSACTION ..... COMMIT" to the stored procedure

This is not a correct way to solve the connection being open issue, this was incorrect suggestion. Did you read my answer, the connection is not being closed from app side and hence locks are being held

m 4,271 Reputation points

2020-11-18T06:19:39.847+00:00

Hope you @Shashank Singh can give one useful and correct reply to help user to solve the issue.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shashank Singh 6,251 Reputation points

2020-11-17T05:42:21.697+00:00

My application is a client-server application, the table lock is not released after running the stored procedure. What's the possible cause? Thank you!

Your application is not closing the connection, this is the issue which is visible to me. Can you get this checked with app owner.
Please sign in to rate this answer.
Rachel 1 Reputation point

2020-11-17T06:20:30.84+00:00

Yes, the connection is not closed immediately after running the stored procedure because the application uses a connection pool to manage these connections.

The table lock should be released after the transactions are committed, right? I added "BEGIN TRANSACTION ..... COMMIT" in the stored procedure, but the lock is still there.

Shashank Singh 6,251 Reputation points

2020-11-17T06:36:43.493+00:00

Ok seems like something fishy, can you please check Unintended Side Effects – Sleeping Sessions Holding Locks

Rachel 1 Reputation point

2020-11-18T03:53:24.043+00:00

Thank you for all the help! I found a similar question in StackOverflow: https://stackoverflow.com/questions/34674039/sql-transaction-doesnt-release-lock-after-commit

"If @@trancount > 0 rollback" did the trick. Any other solutions? Thank you again for all the help!

BEGIN TRAN

DELETE FROM [retailforecast].[dbo].[arima_forecast2];

insert into arima_forecast2 select * FROM arima_forecast1;

COMMIT TRAN

If @@trancount > 0 rollback

SELECT [time]
,[forecast]
,[lo95]
,[hi95]

FROM [retailforecast].[dbo].[arima_forecast2];

Shashank Singh 6,251 Reputation points

2020-11-18T04:29:42.903+00:00

Aah so it was your SP which did not had error handling properly put. Can you show me your SP then I can give you error handling code

Rachel 1 Reputation point

2020-11-18T20:38:08.253+00:00

USE [retailforecast]
GO
/****** Object: StoredProcedure [dbo].[predict_save] Script Date: 2020-11-17 19:54:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[predict_save] (@param1 INT,@param2 INT)
AS
BEGIN

BEGIN TRANSACTION

DELETE FROM [retailforecast].[dbo].[arima_forecast1];

DECLARE @alenzi nvarchar(max)
SET @alenzi ='insert into arima_forecast1 select a.*
FROM OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',
''EXEC [retailforecast].dbo.spR_STL_ARIMA @paramID1='+convert(varchar(10),@param1)+', @paramID2=' +convert(varchar(10),@param2)+''') AS a'

EXEC (@alenzi )

COMMIT

If @@trancount > 0 commit

SELECT [time]
,[forecast]
,[lo95]
,[hi95]

FROM [retailforecast].[dbo].[arima_forecast1]

END

Rachel 1 Reputation point

2020-11-18T20:39:42.537+00:00

I just added " If @@trancount > 0 commit" to make sure all the transactions are committed. This fixed the table block issue.

Dan Guzman 9,236 Reputation points

2020-11-18T23:04:37.22+00:00

Be aware that one should manage transactions in the application layer or T-SQL, but not both. The root cause of the problem is the app is starting a transaction but not committing. I suggest you run a trace to identify the cause. This could be due to an explict BEGIN TRAN in the app code or due to an SET IMPLICT_TRANSACTIONS ON; session setting.

Adding the conditional commit to the proc may "fix" the blocking but can introduce data integrity issues due to committing the transaction without the app knowing about it.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Table lock is not released after running stored procedure

4 answers

Your answer