t-SQL compound statement causes the deadlock, any

Question

I'm in the process of learning a more complex SQL Server 2008 techniques so I apologize in advance if I ask a too obvious question.

I have the following table created as such:

CREATE TABLE [dbo].[t_Log_2] 
(
   [id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
   [oid] INT, 
   [idtm] DATETIME2, 
   [odtm] DATETIME2, 
   [type] TINYINT, 
   [state] TINYINT, 
   [huid] UNIQUEIDENTIFIER, 
   [cnm] NVARCHAR(256), 
   [cmdl] NVARCHAR(256), 
   [batt] TINYINT, 
   [dvtp0] SMALLINT, 
   [dvtp1] SMALLINT
);

CREATE INDEX idx_idt 
          ON [dbo].[t_Log_2]([idtm]);

CREATE INDEX idx_odt 
          ON [dbo].[t_Log_2]([odtm]);

CREATE INDEX idx_huid 
          ON [dbo].[t_Log_2]([huid]);

CREATE INDEX idx_cnm 
          ON [dbo].[t_Log_2]([cnm]);

And then the following query can be run from several simultaneous threads from an ASP.NET web application. Note that this whole query needs to run atomically:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

DELETE FROM [dbo].[t_Log_2] 
      WHERE [idtm]<'2011-03-12 08:41:57';

WITH ctx AS(
     SELECT MIN([idtm]) AS mdIn, 
            MAX([odtm]) AS mdOut 
           FROM [dbo].[t_Log_2] 
          WHERE [type] = 0 
            AND [state] = 0 
            AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
            AND [odtm] >= '2013-03-11 06:33:32' 
            AND [idtm] <= '2013-03-11 06:43:12' 
           ) 
INSERT INTO [dbo].[t_Log_2] 
([oid],[idtm],[odtm],[type],[state],[huid],
 [cnm],[cmdl],[batt],[dvtp0],[dvtp1]) 
SELECT 
    2, 
    CASE WHEN mdIn IS NOT NULL 
          AND mdIn < '2013-03-11 06:33:32' 
         THEN mdIn 
         ELSE '2013-03-11 06:33:32' 
         END,
    CASE WHEN mdOut IS NOT NULL 
          AND mdOut > '2013-03-11 06:43:12' 
         THEN mdOut 
         ELSE '2013-03-11 06:43:12' 
         END,
    0,
    0,
    N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4',
    null,
    null,
    0,
    1,
    null 
FROM ctx 

SELECT ROWCOUNT_BIG()

DELETE FROM [dbo].[t_Log_2] 
      WHERE [type] = 0 
        AND [state] = 0 
        AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
        AND [odtm] >= '2013-03-11 06:33:32' 
        AND [idtm] <= '2013-03-11 06:43:12' 
        AND [id] <> SCOPE_IDENTITY()

DELETE FROM [dbo].[t_Log_2] 
      WHERE [type] = 0 
        AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
        AND [idtm] >= (SELECT [idtm] FROM [dbo].[t_Log_2] 
                                    WHERE [id] = SCOPE_IDENTITY()) 
        AND [odtm] <= (SELECT [odtm] FROM [dbo].[t_Log_2] 
                                    WHERE [id] = SCOPE_IDENTITY()) 
        AND [id] <> SCOPE_IDENTITY() 

;WITH ctx1 AS( 
     SELECT [idtm] AS dI 
       FROM [dbo].[t_Log_2] 
      WHERE [id] = SCOPE_IDENTITY() 
             )
UPDATE [dbo].[t_Log_2] 
        SET [odtm] = ctx1.dI 
       FROM ctx1 
      WHERE [id] <> SCOPE_IDENTITY() 
        AND [type] = 0 
        AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
        AND [idtm] < ctx1.dI 
        AND [odtm] > ctx1.dI 

;WITH ctx2 AS(
     SELECT [odtm] AS dO 
       FROM [dbo].[t_Log_2] 
      WHERE [id] = SCOPE_IDENTITY() 
             ) 
UPDATE [dbo].[t_Log_2] 
        SET [idtm] = ctx2.dO 
       FROM ctx2 
      WHERE [id] <> SCOPE_IDENTITY() 
        AND [type] = 0 
        AND [huid] = N'18ef4d56-6ef3-906a-a711-88d1bd6ab2d4' 
        AND [idtm] < ctx2.dO 
        AND [odtm] > ctx2.dO 

COMMIT TRANSACTION;
SET XACT_ABORT OFF

Note that the query above was copied 1-to-1 from the C# code that dynamically composes it. In reality its parameters are not hard-coded as is shown above.

This query works in most times but once in a while I get the following error in the log:

Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Any idea what shall I do to prevent this deadlock?

Answer 1

You either need to hold more locks or fewer.

The easiest answer is to go either NOLOCK (best performance) or TABLOCKX (consistency without having to think).

If you cannot use with (nolock) because of consistency requirements, you can add with (tablockx). This will effectively mean that only one thread can execute like statements at a time - there will be no concurrency.

The alternative is to analyse your requirements in a lot more detail, which cannot be done without an understanding why you are updating the table, what the data is for etc.

For example, does this statement really need to be in the transaction? It smells like housekeeping:

  DELETE FROM [dbo].[t_Log_2] 
  WHERE [idtm]<'2011-03-12 08:41:57';

If you take that out of the transaction, and put it in a separate batch, you may find the problems go away.