CODEWITHSUNDEEP
sqlt-sql
Valeklosse
Valeklosse

Reputation: 1017

SQL Delete Statement Didnt Delete

Just want to get some views/possible leads on an issue I have.

I have a stored procedure that updates/deletes a record from a table in my database, the table it deletes from is a live table, that temporary holds the data, and also updates records on a archive table. (for reporting etc..) it works normally and havent had an issues.

However recently I had worked on a windows service to monitor our system (running 24/7), which uses a HTTP call to initiate a program, and once this program has finished it then runs the mention stored procedure to delete out redundant data. Basically the service just runs the program quickly to make sure its functioning correctly.

I have noticed recently that the data isnt always being deleted. Looking through logs I see no errors being reported. And Even see the record in the database has been updated correctly. But just doesnt get deleted.

This unfortunately has a knock on effect with the monitoring service, as this continously runs, and sends out alerts because the data cant be duplicated in the live table, hence why it needs to delete out the data.

Currently I have in place a procedure to clear out any old data. (3 hours).

Result has the value - Rejected.

Below is the stored procedure:

    DECLARE @PostponeUntil DATETIME;
DECLARE @Attempts INT;
DECLARE @InitialTarget VARCHAR(8);
DECLARE @MaxAttempts INT;
DECLARE @APIDate DATETIME;

--UPDATE tCallbacks SET Result = @Result WHERE CallbackID = @CallbackID AND UPPER(Result) = 'PENDING';

UPDATE tCallbacks SET Result = @Result WHERE ID = (SELECT TOP 1 ID FROM tCallbacks WHERE CallbackID = @CallbackID ORDER BY ID DESC)

SELECT @InitialTarget = C.InitialTarget, @Attempts = LCB.Attempts, @MaxAttempts = C.CallAttempts
FROM tConfigurations C WITH (NOLOCK)
LEFT JOIN tLiveCallbacks LCB ON LCB.ID = @CallbackID
WHERE C.ID = LCB.ConfigurationID;

IF ((UPPER(@Result) <> 'SUCCESSFUL') AND (UPPER(@Result) <> 'MAXATTEMPTS') AND (UPPER(@Result) <> 'DESTBAR') AND (UPPER(@Result) <> 'REJECTED')) BEGIN
    --INSERT A NEW RECORD FOR RTNR/BUSY/UNSUCCESSFUL/REJECT
    --Create Callback Archive Record

    SELECT @APIDate = CallbackRequestDate FROM tCallbacks WHERE Attempts = 0 AND CallbackID = @CallbackID;

    BEGIN TRANSACTION 
    INSERT INTO tCallbacks (CallbackID, ConfigurationID, InitialTarget, Agent, AgentPresentedCLI, Callee, CalleePresentedCLI, CallbackRequestDate, Attempts, Result, CBRType, ExternalID, ASR, SessionID)
        SELECT ID, ConfigurationID, @InitialTarget, Agent, AgentPresentedCLI, Callee, CalleePresentedCLI, @APIDate, @Attempts + 1, 'PENDING', CBRType, ExternalID, ASR, SessionID
        FROM tLiveCallbacks
        WHERE ID = @CallbackID;

    UPDATE LCB 
    SET PostponeUntil = DATEADD(second, C.CallRetryPeriod, GETDATE()),
        Pending = 0,
        Attempts = @Attempts + 1
    FROM tLiveCallbacks LCB
    LEFT JOIN tConfigurations C ON C.ID = LCB.ConfigurationID
    WHERE LCB.ID = @CallbackID;

    COMMIT TRANSACTION
END
ELSE BEGIN
    -- Update the Callbacks archive, when Successful or Max Attempts or DestBar.
    IF EXISTS (SELECT ID FROM tLiveCallbacks WHERE ID = @CallbackID) BEGIN

        BEGIN TRANSACTION
        UPDATE  tCallbacks 
        SET Attempts = @Attempts 
        WHERE ID IN (SELECT TOP (1) ID 
                             FROM tCallbacks 
                             WHERE CallbackID = @CallbackID 
                             ORDER BY Attempts DESC);

        -- The live callback should no longer be active now. As its either been answered or reach the max attempts.
        DELETE FROM tLiveCallbacks WHERE ID = @CallbackID;
        COMMIT
    END
END

Upvotes: 0

Views: 154

Answers (1)

HLGEM
HLGEM

Reputation: 96552

You need to fix your transaction processing. What is happening is that one statement is failing but since you don't have a try-catch block all changes are not getting rolled back only the statement that failed.

You should never have a begin tran without a try catch block and a rollback on error. I personally also prefer in something like this to put the errors and associated data into a table variable (which will not rollback) and then insert then to an exception table after the rollback. This way the data retains integrity and you can look up what the problem was.

Upvotes: 2

Related Questions