CODEWITHSUNDEEP
sql-serversql-deleteblocking
Stefan
Stefan

Reputation: 9

Skipping locked rows when deleting data in SQL Server

I got a table in an ERP system where lot of old data resides. I got a job which detects and deletes that outdated data.

However, some of that data is locked by the ERP system and therefore cannot be deleted. Unfortunately my delete job is waiting ages before that data eventually gets unlocked.

I tried to write an delete statement which skips those locked records, but with no success. I tried NOLOCK and READPAST hints, but they did not work. The delete statement still was blocked.

Is there any hint or other means available to perform this?

Upvotes: 0

Views: 91

Answers (1)

zackychan97
zackychan97

Reputation: 146

There is not much of a way, as Thom A mentions in comment, it's not how SQL Server works. There are ways to check if a table has locks inside of it, but not the individual rows to my knowledge.

You could attack this a couple ways, and perhaps even intertwine the approaches.

One way would be to try and delete from the table. Something to the effect of:

DECLARE @MaxRetries INT = 5;
DECLARE @RetryCount INT = 0;
DECLARE @WaitTime INT = 10; -- seconds

WHILE @RetryCount < @MaxRetries
BEGIN
    BEGIN TRY
        -- Attempt the delete operation
        DELETE FROM YourTable
        WHERE YourCondition;

        -- Exit the loop if successful
        BREAK;
    END TRY
    BEGIN CATCH
        -- Handle lock exception or log it
        IF ERROR_NUMBER() IN (1205, 1222) -- Error numbers for deadlocks and lock timeouts
        BEGIN
            SET @RetryCount = @RetryCount + 1;
            WAITFOR DELAY '00:00:' + CAST(@WaitTime AS VARCHAR(2));
        END
        ELSE
        BEGIN
            -- Rethrow the exception if it's not a lock error
            THROW;
        END
    END CATCH
END

Another thing to look into:

 SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> }

This could be dangerous though as I have 0 knowledge of your system and the reason the locks are occurring. There is potential that other process causing locks are set to HIGH priority here, and maybe for good reason.

Upvotes: 0

Related Questions