T-SQL update deadlock concurrency issue

Question

I have 2 jobs scheduled at the same time 9am both calling using a loop container which downloads a SSRS report per iteration, but the job reported a deadlock on a stored procedure used in the process that gets the ID of the next report off the rank to run and it tags the row that it is in use by updating a status column.

The stored procedure:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN
    DECLARE @ReportID INT

    SELECT TOP 1 @ReportID = SSRSReportId
    FROM dbo.ReportAutomationThreadQueue WITH (UPDLOCK)
    WHERE ReportRunName = @ReportRunName 
      AND ExtractStatus IS NULL

    UPDATE dbo.ReportAutomationThreadQueue
    SET ExtractStatus = 'Pending',
        ThreadInstanceNumber = @ThreadInstanceNumber
    WHERE ReportRunName = @ReportRunName 
      AND SSRSReportId = @ReportID

    COMMIT TRAN

After the transaction it just returns the rows including report parameter information or nothing if nothing more left in the queue:

    SELECT 
            SSRSReportId,
            RelativePath,
            FileName,
            ParameterId,
            ParameterName,
            ParameterValue
    FROM dbo.ReportAutomationThreadQueue
    WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID

As I see it this should never deadlock as the second call must wait on the SELECT statement before proceeding until the first process finishes its update.

2 jobs will have different @ReportRunName values. Do I need a RowLock or a Holdlock perhaps?

How do I simulate as running 2 jobs at the same time I could try a million times, and it will not clash. Maybe I could try a delay perhaps to guarantee a lock. I am trying to figure out if the deadlock occurred in the same job or separate jobs.

The job itself for a single ReportRunName that can download at most 10 reports does not take that long a couple of minutes or less to run as it is calling config and reports from a DWH so not operational so, you might say just reschedule the second job to be a 5 minutes later. But I am still curious to know what or how to fix.

The only other thing to add of the 2 jobs that were scheduled to run at the same time, one of them finished successfully in 1:04 mins and the second job chosen as the deadlock victim stopped after 12 secs. Does this mean it took 12 seconds of initialisation in the job before it called the stored procedure for the first time or perhaps the first report only took 12 seconds to download, and the deadlock might have occurred on the second time around the loop. I cannot believe the same job would cause a deadlock so it must be the fact 2 jobs are running at the same time. I am dismissing any third possibility of some other process running I am not aware of causing it.

This code takes exclusive locks on far more than it needs. The only way to repeatedly get the same 1 item is to lock the matched rows, not just the single result. You can replace both queries with an UPDATE TOP 1 ... OUTPUT inserted.SSRSReportId ... This will both update a single row and return its ID — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 23 at 8:56
I have had this process running for over a year now and this deadlock only occurred for the first time. I cannot reproduce that is why I mentioned about how do I simulate it. Adding in a delay I tried and with current code there it gets the correct values and no deadlock so I cannot set up profiler to obtain the deadlock graph until I learn how to reproduce in a non prod environment. — Glen
– Glen, Commented Jan 24 at 0:16

Dale K · Accepted Answer · 2025-01-23 08:58:43Z

2

Choosing single random item with empty status, not locked by any other process and marking it as occupied by a process simultaneously, can be accomplished in more simple way:

UPDATE TOP(1) rtq
SET ExtractStatus = 'Pending',
    ThreadInstanceNumber = @ThreadInstanceNumber,
    @ReportID = rtq.SSRSReportId
FROM dbo.ReportAutomationThreadQueue AS rtq WITH (READPAST)
WHERE ReportRunName = @ReportRunName 
    AND ExtractStatus IS NULL

TOP(1) will update any first row matching given conditions
READPAST will ignore locked rows
variable assignment in SET clause will save the updated row ID value

No explicit transaction, multiple statements, isolation harder than read committed needed for your task. Note, IF @@ROWCOUNT = 0 should be handled manually after the query.

Anyways, actual execution plan and deadlock graph reviews are still required: poor DB design and indexing can still lead to some locking troubles.

edited Jan 23 at 8:58

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Jan 23 at 8:46

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Panagiotis Kanavos Jan 23 at 8:54

To retrieve the affected IDs it's better to use an OUTPUT clause. @@ROWCOUNT isn't strictly needed as @ReportID` will have its initial value, NULL.

Glen Jan 23 at 23:25

How do I return the actual rows i.e. SELECT * FROM dbo.ReportAutomationThreadQueue WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID? As this could be multiple rows as each row is a parameter list you feed into the report. Sorry I guess I should have added that rather than left it as implied

Glen Jan 23 at 23:30

And I am not updating one row could be multiple

Glen Jan 23 at 23:40

This is a not an operation database just a configuration database so less than 50 rows and I have no index. I guess I could add one to the thread table as in RunReportName, SSRSReportId and ParameterId!

Glen Jan 24 at 5:50

Thanks for ReadPast hint as I think it worked well at least in my own mind. I will explain in detail by answering my own question

Glen · Accepted Answer · 2025-01-24 06:08:42Z

Ok I got it working at last well I think I have. I added RAISERROR(...) WITH NOWAIT after every line of SQL code.

I kept the UPLOCK where it was, removed the ISOLATION and added a READPAST on the UPDATE statement and it seems to work!

When I added the delay between the SELECT and the UPDATE and ran it in 2 different sessions (windows in SSMS) I can physically see that the first session got to the delay line but the second session was halted at the SELECT statement and only proceeded after the first session completed. Both sessions return the correct ReportId, i.e. unique id's.

Without stuffing up data in my UAT environment I simulated using a dummy table.

CREATE OR ALTER PROCEDURE [dbo].[spTest]
    -- Add the parameters for the stored procedure here
    @ReportRunName VARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
RAISERROR('Start',0,1)WITH NOWAIT
    --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
--RAISERROR('ISOLATION LEVEL REPEATABLE READ is set',0,1)WITH NOWAIT
    BEGIN TRAN
RAISERROR('Transaction started',0,1)WITH NOWAIT
    DECLARE @ReportID INT
    SELECT TOP 1 @ReportID = SSRSReportId
    FROM dbo.Test1 WITH (UPDLOCK)
    WHERE ReportRunName = @ReportRunName And ExtractStatus IS NULL
RAISERROR('Retrieved latest ReportId: %d',0,1,@ReportID)WITH NOWAIT
IF @ReportID = 1
BEGIN
    RAISERROR('Delay Issued',0,1)WITH NOWAIT
    WAITFOR DELAY '00:00:05';
    RAISERROR('Delay Complete',0,1)WITH NOWAIT
END

    UPDATE t
    SET ExtractStatus = 'Pending'
    FROM dbo.Test1 t WITH (READPAST)
    WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID
RAISERROR('Update Completed',0,1)WITH NOWAIT
    COMMIT TRAN
RAISERROR('Transaction Finished',0,1)WITH NOWAIT
    SELECT * FROM dbo.Test1 WHERE ReportRunName = @ReportRunName And SSRSReportId = @ReportID
END
GO


TRUNCATE TABLE dbo.Test1
INSERT INTO dbo.Test1 VALUES
('BOGUS', 1, NULL),
('BOGUS', 2, NULL)
SELECT *
FROM dbo.Test1

Ran the proc in 2 different sessions

EXEC [dbo].[spTest] 'BOGUS'

Session1 output:

Transaction started
Retrieved latest ReportId: 1 
Delay Issued
Delay Complete Update Completed Transaction Finished
 
Completion time: 2025-01-24T16:36:59.4277305+11:00

Session2 output:

Transaction started -halted here
Retrieved latest ReportId: 2
Update Completed
Transaction Finished

Completion time: 2025-01-24T16:36:59.4451237+11:00

Collectives™ on Stack Overflow

T-SQL update deadlock concurrency issue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related