BLocking issue

• raghuraman_kumarasam

  Valued Member

  Points: 59

  More actions

  September 5, 2012 at 8:52 am

  #393106

  hi,

  Summary: I'm facing a dealock issue. 2 procedures are using update statements on request_info table and each blocking each other. I dont know what the issue is, why it is blocking? please help. here are the details.

  Table :

  request_info(request_id long, request_type_id int, business_unit_id int, processorstate int)

  processorstate -0 - not picked up for processiong

  processorstate -1 - already picked up for processing.

  I've 2 procedures{proc_GetRequests,proc_GetMassRequest} to pick up the latest request ids for single request(request_type_id : 1,2,3,4,5,6,7) and other one for mass request(request_type_id: 8,9,10,11).

  ====================================

  1 Procedure: dbo.proc_GetRequests(pick single request and put into temp table,set processorstate to 1 for picked ones):

  ====================================

  -- to make processorstate to 1 for non automation

  UPDATE request_info SET ProcessorState=1 WHERE ProcessorState not in (1) and request_type_id not in (1,2,3,4,5,6,7,8,9,10,11,12,20,21)

  DECLARE @RequestProcess table(

  requestId bigint NOT NULL,

  requestTypeId int,

  businessUnitId int);

  UPDATE TOP (3) request_info SET ProcessorState=1 OUTPUT inserted.request_id,inserted.request_type_id,inserted.business_unit_id

  INTO @RequestProcess WHERE ProcessorState not in (-1,1)

  and request_type_id in (1,2,3,4,5,6,7,12,20,21)

  --do processing

  end

  ======================================

  2 Procedure: proc_GetMassRequest(pick mass request put into temp table,set processorstate to 1 for picked ones):

  ======================================

  DECLARE @RequestProcess table(

  requestId bigint NOT NULL,

  requestTypeId int,

  businessUnitId int);

  UPDATE TOP (3) request_info SET ProcessorState=1 OUTPUT inserted.request_id,inserted.request_type_id,inserted.business_unit_id

  INTO @RequestProcess WHERE ProcessorState not in (-1,1)

  and request_type_id in (8,9,10,11)

  --do processing

  end

  Two procs are called from java and might run at the same time. NOw i'm facing a deadlock.

  i see this in SP_WHO2 for blocking

  253 SUSPENDED xyz 258 Harmony_Global UPDATE 15 1 09/05 04:28:57 i-net OPTA 2000 253 0

  253 - Harmony_Global.dbo.proc_GetMassRequest;1

  258 - Harmony_Global.dbo.proc_GetRequests;1

  Why there is a deadlock? is this because, i use update top 3? instead should i get the requests and then update against the request_id?

  i want to keep both proc. separate and need a solution.

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  September 5, 2012 at 9:23 am

  #1533927

  Here's an article I found helpful when troubleshooting Deadlocks:

  http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/

  _____________________________________________________________________
  - Nate

  @nate_hughes