0

My windows service, written in C# occasionally (few times a week) logs a message like this:

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

getClientData is my own C# function that simply calls a stored procedure. The stored procedure is an implementation of priority+pending queue in mssql. I got the idea from here.

I have just found this explanation of how a deadlock can happen without transactions in a simple select query (+additional things happening in background). Now, these deadlocks aren't really that big of a deal, because that does not crash my service (i simply retry the queries), but I would like to know if it's possible to get rid of these deadlocks.

Oh and, the data is inserted into [dbo].[tbl_client_data] table one row at a time from several different sources (they are all just simple one row inserts, though).

Stored procedure looks pretty much like this (there are more data columns, but that seems irrelevant to me):

CREATE PROCEDURE [dbo].[sp_get_client_data]
    @client_id [int],
    @limit [int] = 0
AS
BEGIN
    IF(@limit < 0) SET @limit = 0;

    DECLARE @data TABLE
    (
        [id] [int],
        [data] [varchar](max),
        [client_id] [int],
        [ts] [datetime],
        [priority] [tinyint]
    );

    IF(@limit > 0)
    BEGIN
        DELETE TOP(@limit)
          FROM [dbo].[tbl_client_data]
        OUTPUT DELETED.[id]
              ,DELETED.[data]
              ,DELETED.[client_id]
              ,DELETED.[ts]
              ,DELETED.[priority]
          INTO @data
         WHERE [id] IN (SELECT TOP(@limit) [id]
                          FROM [dbo].[tbl_client_data] with(nolock)
                         WHERE ([ts] IS NULL OR [ts] <= GETDATE())
                           AND [client_id] = @client_id
                        ORDER BY [priority], [ts]);
    END

    SELECT *
      FROM @data
    ORDER BY [priority], [ts];
END

So my question is - Is there a way to get rid of these occasional (very rare) deadlocks, or should I just ignore them and simply retry the queries as I do know?

Community
  • 1
  • 1
Paulius
  • 5,790
  • 7
  • 42
  • 47
  • 1
    This is one of those "can of worms" questions. Prepare yourself. – rory.ap Jun 02 '15 at 12:49
  • 1
    There's a few different ways to [capture deadlocks in SQL Server](http://www.brentozar.com/archive/2014/06/capturing-deadlock-information/). If it was me, I'd wanna see some deadlock graphs first. – Dave Mason Jun 02 '15 at 12:55
  • I've been running profiler for about 12 hours now, but no deadlocks so far. As I said in the question - they are really rare events, hard to reproduce. – Paulius Jun 02 '15 at 13:11
  • Why the nested query for the ID's? Why not write one `DELETE` statement? – Panagiotis Kanavos Jun 02 '15 at 13:28
  • @PanagiotisKanavos Because I can't use `ORDER BY` in `DELETE TOP(...)` statement. – Paulius Jun 02 '15 at 13:50
  • 1
    Are you trying to emulated a priority queue? This is *not* trivial, I've been bitten in the past. Try using the `rowlock,readpast` hints to simply avoid waiting on locked rows. Convert the select to a CTE so that select rows are *not* acquired on the table. The article [Using tables as queues](http://rusanu.com/2010/03/26/using-tables-as-queues/) compares many options, wish I'd read it before trying the same – Panagiotis Kanavos Jun 02 '15 at 14:02
  • You can consider turning on READ COMMITTED SNAPSHOT (but investigate this thoroughly to understand the changes it makes) but if deadlocks are as rare as you seem to suggest then I would simply catch the exception and retry the transactions. I mean, are you expecting to increase your load by an order of magnitude, or are things running as heavy as they're going to get for the foreseeable future? – Bacon Bits Jun 02 '15 at 14:20

0 Answers0