Sunday, July 17, 2016

Sql Server tSql: DELETE statement executes but won't delete

Problem

I ran into the most puzzling issue recently where I was executing a DELETE statement inside of a transaction among other statements inside of a stored procedure. Then the weirdest most puzzling thing happened, the statement executed, but would not delete rows. It was very bizarre. Then when the whole process was finished if I executed the statement afterwards it worked. I thought maybe it had something to do with executing inside of a transaction, so I took the statement out of the transaction and it still didn't work. The syntax you are going to see is in SQL Server 2005 format because that is what I had to support, but I was running my tests on SQL Server 2014.

Long story short, after several hours of frustration, I figured it out.

Gotcha

Before I get to the gotcha, I just want to point out that I am a Software Architect with a ton of exposure to tSql, but I am not a DBA so some of these core SQL concentric problems still allude me at times. I always appreciate these moments of absolute clarity due to epiphany, it's a good feeling. 
Moving along to the gotcha... It turns out that the tables I was working on had triggers and constraints with cascading deletes enabled. This was really causing some seemingly strange behavior during execution. Obviously there is nothing strange about the execution after I learned that these tables had these other objects buzzing away in the background during the execution of my DELETE statements.

I disabled all triggers and constraints (specifically with cascading deletes enabled) associated with the target tables and the strange behavior disappeared and the deletes actually executed. One of the strangest parts about this is that there were no triggers directly on the target tables, so I don't understand what was blocking the delete.

What was most frustrating about this experience is that I would not see an error, everything just appeared as though it executed.

Delete Example

Everything is always better with an example so here it is. Imagine you have a huge table that needs to be pruned. You are given a list of criteria, but really it boils down to batch deleting rows from a target table.

DECLARE @batchSize INT = 5000;
DECLARE @rowCount INT = 1;

WHILE @rowCount > 0
BEGIN
 DELETE TOP(@batchSize) t
 FROM dbo.Table1 t

 SET @rowCount = @@ROWCOUNT;
END

The amount being deleted is truly arbitrary, so I just chose 5K for the sake of argument. Let's say this is a special table that has a lot of things happening to it implicitly and indirectly, much like many things in the magic world of SQL engines. Some of these things are constraints and some of these things are triggers, therefore in order to successfully delete those rows above, these constraints and triggers must be temporarily disabled. This of course leads to a new host of problems such as - should you be running this in single user mode and things of that nature. The answer is yes, now how you convince your co-workers, boss and especially your DBA of this is up to you I cannot help you with this. Especially if you have to tear down replication and rebuild it. Have fun with that.

Toggling Constraints

Turns your constraints on and off for a pre-defined set of tables. The sproc following this one really isn't all that much different except for the syntax of the command being dynamically constructed. Really this is straight-forward.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[sp_ToggleConstraints] 1
-- [dbo].[sp_ToggleConstraints] 0
CREATE PROCEDURE [dbo].[sp_ToggleConstraints]
(
 @isEnabled BIT
)
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @keywords VARCHAR(50);

 DECLARE @sqlStatements TABLE
 ( 
   EntryId INT IDENTITY(0,1)
  ,SqlStatement VARCHAR(255)
 )

 IF @isEnabled = 1
  SET @keywords = ' WITH NOCHECK CHECK CONSTRAINT '
 ELSE
  SET @keywords = ' NOCHECK CONSTRAINT '

 /* Examples: 
   Disable: ALTER TABLE dbo.[TargetTableName] NOCHECK CONSTRAINT [FK name]
   Enable : ALTER TABLE dbo.[TargetTableName] WITH CHECK CHECK CONSTRAINT [FK name]
 */

 -- Select your dynamically created SQL statements for constraints
 INSERT INTO @sqlStatements
 SELECT 
  sqlConstraint = 'ALTER TABLE dbo.' + x.TableName + @keywords + x.ConstraintName
 FROM
 (
  SELECT
    TableName = t.name
   ,ConstraintName = fk.name
  FROM [dbo].[TableTarget] att -- It's a good idea to know what you are targetting

   INNER JOIN sys.tables t 
    ON t.name = att.TableName
   INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
     AND s.name = 'dbo'
   INNER JOIN sys.foreign_keys fk
    ON fk.parent_object_id = t.object_id
     AND fk.delete_referential_action = 1 -- Targetting constraints with cascading deletes only
  WHERE att.ToggleConstraints = 1 -- Only select tables that should have their constraints toggled
  
  -- Sometimes tables are so poorly designed that all constraints have to be toggled
  UNION SELECT
    TableName = 'ScrewedUpTable1'
   ,ConstraintName = 'ALL'
  UNION SELECT
    TableName = 'ScrewedUpTable2'
   ,ConstraintName = 'ALL'
 ) as x
 ORDER BY
  x.TableName

 --SELECT * FROM @sqlStatements
 DECLARE @i INT;
 SET @i = 0;

 DECLARE @count INT;

 SELECT @count = COUNT(1)
 FROM @sqlStatements

 DECLARE @str VARCHAR(255)

 WHILE @i < @count
 BEGIN
  SELECT 
   @str = SqlStatement
  FROM @sqlStatements
  WHERE EntryId = @i;
  
  PRINT @str

  BEGIN TRY
   EXEC(@str);
  END TRY
  BEGIN CATCH
   -- declare the variables
   DECLARE @errorNumber INT
   ,@errorSeverity INT
   ,@errorState INT
   ,@errorProcedure nvarchar(128)
   ,@errorLine INT
   ,@errorMessage nvarchar(4000)

   -- Store the values
   SET @errorNumber = ERROR_NUMBER()
   SET @errorSeverity = ERROR_SEVERITY()
   SET @errorState = ERROR_STATE()
   SET @errorProcedure = ERROR_PROCEDURE()
   SET @errorLine = ERROR_LINE()
   SET @errorMessage = ERROR_MESSAGE()
 
   -- Check the values
   SELECT
    Number = @errorNumber
   ,Severity = @errorSeverity 
   ,[State] = @errorState
   ,[Procedure] = @errorProcedure
   ,Line = @errorLine
   ,[Message] = @errorMessage

   -- Log the error
   EXEC yourLoggingSprocGoesHere_youShouldReallyHaveOnIfYouDont
    @errorNumber = @errorNumber
   ,@errorSeverity = @errorSeverity
   ,@errorState = @errorState
   ,@errorProcedure = @errorProcedure
   ,@errorLine = @errorLine
   ,@errorMessage = @errorMessage
  END CATCH

  SET @i = @i + 1;
 END
END
GO

Toggling Triggers

Turn your triggers on and off. Very much like the previous sproc.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[sp_ToggleTriggers] 1
-- [dbo].[sp_ToggleTriggers] 0
CREATE PROCEDURE [dbo].[sp_ToggleTriggers]
(
 @isEnabled BIT
)
AS
BEGIN
 SET NOCOUNT ON;
 
 DECLARE @keyword VARCHAR(7);

 IF @isEnabled = 1
  SET @keyword = 'ENABLE'
 ELSE
  SET @keyword = 'DISABLE'
 
 DECLARE @sqlStatements TABLE
 ( 
   EntryId INT IDENTITY(0,1)
  ,SqlStatement VARCHAR(255)
 )

 /* Examples: 
   ENABLE  TRIGGER dbo.[triggerName] ON dbo.[targetTableName]
   DISABLE TRIGGER dbo.[triggerName] ON dbo.[targetTableName]
 */
 INSERT INTO @sqlStatements
 SELECT
  sqlTrigger = @keyword + ' TRIGGER dbo.' + g.name + ' ON dbo.' + t.name
 FROM [dbo].[TableTarget] att -- It's a good idea to know what you are targetting
  INNER JOIN sys.tables t
   ON t.name = att.TableName
  INNER JOIN sys.schemas s
   ON t.schema_id = s.schema_id
    AND s.name = 'dbo'
  INNER JOIN sys.triggers g
   ON g.parent_id = t.object_id
 WHERE g.name LIKE 'trfs%' -- You may have to filter your triggers if you use replication. Mine happen to start with "trfs"
 ORDER BY g.name

 --SELECT * FROM @sqlStatements
 DECLARE @i INT;
 SET @i = 0;

 DECLARE @count INT;

 SELECT @count = COUNT(1)
 FROM @sqlStatements

 DECLARE @str VARCHAR(255)

 WHILE @i < @count
 BEGIN
  SELECT 
   @str = SqlStatement
  FROM @sqlStatements
  WHERE EntryId = @i;
  
  PRINT @str

  BEGIN TRY
   EXEC(@str);
  END TRY
  BEGIN CATCH
   -- declare the variables
   declare @errorNumber INT
   ,@errorSeverity INT
   ,@errorState INT
   ,@errorProcedure nvarchar(128)
   ,@errorLine INT
   ,@errorMessage nvarchar(4000)

   -- Store the values
   SET @errorNumber = ERROR_NUMBER()
   SET @errorSeverity = ERROR_SEVERITY()
   SET @errorState = ERROR_STATE()
   SET @errorProcedure = ERROR_PROCEDURE()
   SET @errorLine = ERROR_LINE()
   SET @errorMessage = ERROR_MESSAGE()
 
   -- Check the values
   SELECT
    Number = @errorNumber
   ,Severity = @errorSeverity 
   ,[State] = @errorState
   ,[Procedure] = @errorProcedure
   ,Line = @errorLine
   ,[Message] = @errorMessage

   -- Log the error
   EXEC yourLoggingSprocGoesHere_youShouldReallyHaveOnIfYouDont
    @errorNumber = @errorNumber
   ,@errorSeverity = @errorSeverity
   ,@errorState = @errorState
   ,@errorProcedure = @errorProcedure
   ,@errorLine = @errorLine
   ,@errorMessage = @errorMessage
  END CATCH

  SET @i = @i + 1;
 END
END
GO

No comments:

Post a Comment