Blocking Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Blocking Issue

Author

Topic

raghukraman
Starting Member

2 Posts

Posted - 2012-09-05 : 10:58:07

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.

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-05 : 11:54:21

This is not a deadlock. Query sys.dm_os_waiting_tasks to see what the query is waiting on.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources