SQL Server 2005 Deadlock Problem

Question

I’m running into a deadlock problem when trying to lock some records so that no process (Windows service) picks the items to service them, then update the status and then return a recordset.

Can you please let me know why am I getting the deadlock issue when this proc is invoked?

CREATE PROCEDURE [dbo].[sp_LoadEventsTemp]
(
   @RequestKey varchar(20),
   @RequestType varchar(20),
   @Status varchar(20),
   @ScheduledDate smalldatetime = null
)
AS
BEGIN
   declare @LoadEvents table
   (
      id int
   )

   BEGIN TRANSACTION

   if (@scheduledDate is null)
   Begin
      insert into @LoadEvents (id)
      (
         Select eventqueueid FROM eventqueue
         WITH (HOLDLOCK, ROWLOCK)
         WHERE requestkey = @RequestKey
         and requesttype = @RequestType
         and [status] = @Status
      )
   END
   else
   BEGIN
      insert into @LoadEvents (id)
      (
         Select eventqueueid FROM eventqueue
         WITH (HOLDLOCK, ROWLOCK)
         WHERE requestkey = @RequestKey
         and requesttype = @RequestType
         and [status] = @Status
         and (convert(smalldatetime,scheduleddate) <= @ScheduledDate)
      )
   END

   update eventqueue set [status] = 'InProgress'
   where eventqueueid in (select id from @LoadEvents)

   IF @@Error 0
   BEGIN
      ROLLBACK TRANSACTION
   END
   ELSE
   BEGIN
      COMMIT TRANSACTION
      select * from eventqueue
      where eventqueueid in (select id from @LoadEvents)
   END
END

Thanks in advance.

Answer 1

Do you have a non-clustered index defined as:

CREATE NONCLUSTERED INDEX NC_eventqueue_requestkey_requesttype_status
    ON eventqueue(requestkey, requesttype, status)
         INCLUDE eventqueueid

and another on eventqueueid?

BTW the conversion of column scheduleddate to smalldatetime type will prevent any use of an index on that column.

Answer 2

First of all, as you're running SQL Server I'd recommend you to intall Performance Dashboard which is a very handy tool to identify what locks are currently being made on the server.

Performance Dahsboard Link

Second, take a trace of your SQL Server using SQL Profiler (Already Installed) and make sure that you select on the Events Selection the item Locks > Deadlock graph which will show what is causing the deadlock.

You got to have very clear on your mind what a deadlock is to start troubleshooting it. When any access is made to any table or row on the DB a lock is made.

Lets call SPID 51 and SPID 52 (SPID = SQL Process ID)

SPID 51 locks Cell A

SPID 52 locks Cell B

if on the same transaction SPID 51 requests for the Cell B, it'll wait SPID 52 till it releases it.

if on the same transaction SPID 52 requests for the Cell A, you got a deadlock because this situation will never finish (51 waiting for 52 and 52 for 51)

Got to tell you that it ain't easy to troubleshoot, but you you to dig deeper to find the resolution

Answer 3

Deadlocks happen most often (in my experience) when differnt resources are locked within differnt transactions in different orders.

Imagine 2 processes using resource A and B, but locking them in different orders.
- Process 1 locks resource A, then resource B
- Process 2 locks resource B, then resource A

The following then becomes possible:
- Process 1 locks resource A
- Process 2 locks resource B
- Process 1 tries to lock resource B, then stops and waits as Process 2 has it
- Process 2 tries to lock resource A, then stops and waits as Process 1 has it
- Both proceses are waiting for each other, Deadlock

In your case we would need to see exactly where the SP falls over due to a deadlock (the update I'd guess?) and any other processes that reference that table. It could be an trigger or something, which then gets deadlocked on a different table, not the table you're updating.

What I would do is use SQL Server 2005's OUTPUT syntaxt to avoid having to use the transaction...

UPDATE
  eventqueue
SET
  status = 'InProgress'
WHERE
      requestkey  = @RequestKey
  AND requesttype = @RequestType
  AND status      = @Status
  AND (convert(smalldatetime,scheduleddate) <= @ScheduledDate OR @ScheduledDate  IS NULL)
OUTPUT
  inserted.*