Problem with Deadlock - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with Deadlock

Author

Topic

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-26 : 21:57:20

System Overview:
1) Users upload their orders to our system by simply emailing the report which is in (.xls) Excel format.
2) Our pickup program downloads the attachment from Exchange and converts it to native file and clean it using Regular Expression and place it on hot folders.
3) Biztalk pick up the file, do the Mapping, passed to the Adapter and to component the process file.
4) Biztalk with the help of component call this Stored Procedure (see code below)

Problem is when we upload simultaneously we have deadlock which is coming from this SP. How can we avoid dealocks and maybe optimized this code.

CREATE PROCEDURE PSODBValidateUploadProc (
@sflid int,
@ssoIDList varchar(4000),
@RetTotal int =0 OUTPUT ,
@RetProcessed int = 0 OUTPUT,
@RetcpID varchar(4) ='' OUTPUT,
@RetVal int = 0 OUTPUT
)
AS
--insert into table1 values(@ssoIDList)
--return

SET NOCOUNT ON
DECLARE @InpDate Datetime
DECLARE @totalCnt int
DECLARE @totalProcessed int
DECLARE @CurrentDate varchar(10)
DECLARE @s int
DECLARE @cpIsUploadtoSBS int
SET @CurrentDate = convert(varchar(10),GetDate(),112)
Select @RetcpID = cpid from SourceFileLog where sflid = @sflid
Select @cpIsUploadtoSBS = cpIsUploadtoSBS from CustomerProfile where cpid = @RetcpID

-- ************** EXECUTE UPDATE ZP Codes *****************
IF (@ssoIDList = '' OR @ssoIDList is null)
BEGIN
EXEC WUSalesB.SetZPCodesUpdProc @sflid, 'O'
END
-- Create Temporary Table for list of SSO ID's
CREATE TABLE #ssoid ([id] INT)

IF @ssoIDList = ''
BEGIN
INSERT INTO #ssoid ([id])
Select ssoID from StgSO where sflid = @sflid and statusID <> 2000

END
ELSE
-- 'ID ARRAY SPLITTER
BEGIN
EXEC GetIDListSelProc @ssoIDList ,'#ssoID'
END
--- TRANSACTION BEGINS
-- ( Step 1 )
-- ********************** UPDATE AND VALIDATE StgSO **************************
IF (@ssoIDList = '' OR @ssoIDList is null)
BEGIN
UPDATE StgSO SET StatusID =
(CASE
WHEN EXISTS(SELECT subcode from SpecialCase where subcode = ZPSubCode and ZPItemCode = StgSo.ZPItemCode and scAction=2015) THEN 2015
WHEN (ZPSubCode = '' OR ZPSubCode IS NULL) THEN 2006
WHEN (ZPItemCode = '' AND ZPCustCode = '' ) OR (ZPItemCode IS NULL AND ZPCustCode IS NULL) THEN 2005
WHEN (ZPCustCode = '' OR ZPCustCode IS NULL) THEN 2003
WHEN (ZPItemCode = '' OR ZPItemCode IS NULL) THEN 2004
WHEN(DocDate > @CurrentDate) THEN 2014
WHEN (StatusID = 2000) THEN 2000
ELSE 2002
END)
WHERE ssoID in (SELECT [id] FROM #ssoID)
END
set @s = @@ROWCOUNT
--- **************************************************************************************************************
--- updates ZPQty against SpecialCase Table
Select @InpDate = sflAddDate from SourceFileLog where sflID = @sflid -- Get Input Date from SourceFileLog

Update StgSO set ZPQty = (
CASE
WHEN SpecialCase.scAction = 0 THEN ( StgSo.Qty * SpecialCase.scValue)
WHEN SpecialCase.scAction = 1 THEN ( StgSo.Qty / SpecialCase.scValue)
END
)
from SpecialCase
WHERE ssoID in (SELECT [id] FROM #ssoID) and
StgSo.ZPSubCode = SpecialCase.subcode and
StgSo.ZPItemCode = SpecialCase.ZPItemCode and
StatusID = 2002
--- **************************************************************************************************************
set @s = @@ROWCOUNT

--************* CHECK IF SOURCE IS FROM BIZTALK, RETURN if @cpIsUploadtoSBS = 0
IF @ssoIDList = ''
BEGIN
if (@cpIsUploadtoSBS=0) RETURN
END

-- (SELECT [id] FROM #ssoid)
SELECT @RetTotal =count([id]) FROM #ssoID
-- Test if Error Occured -- Rollback 1

DECLARE @tobeProc INT
SELECT @tobeProc = count(sflid) from StgSO where StatusID in (2002,2015) and (sflid = @sflid)
if @tobeProc < 1
BEGIN

SET @RetVal = 0
SET @RetProcessed = 0
RETURN
END

if @@ERROR <> 0
BEGIN
update StgSo set statusID = 2010 from #ssoid where StgSo.ssoID = #ssoid.[ID]
DROP TABLE #ssoID
SET @RetVal = -1000 -- update and Validate StgSO
RETURN
END
---- ***************************************************************************************
-- ( Step 2 )
-- ********************** Start Upload StgSO Table to PSO/DB Table*************************
-- Creates Temporary Table
CREATE TABLE #tempPSO (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[WSCode] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[HPCode] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ITEM] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[QTY] [numeric](18, 0) NOT NULL ,
[SalesID] [char] (3) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[OEDate] [datetime] NOT NULL ,
[CFM] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[InpDate] [datetime] NOT NULL ,
[EDI] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[ttID] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[pcdCutOffFlag] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ssoID] [int]
) ON [PRIMARY]
-- Temporary Table
CREATE TABLE #tempDB (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FrWS] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ToWS] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ITEM] [char] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[QTY] [numeric](18, 0) NOT NULL ,
[SENDER] [char] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[DBDate] [datetime] NOT NULL ,
[InpDate] [datetime] NOT NULL ,
[EDI] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[pcdCutOffFlag] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ssoID] [int]
) ON [PRIMARY]

--- PSO Only

Insert into #tempPSO ([WSCode],[HPCode],[ITEM],[QTY],[SalesID],[OEDate],[CFM],[InpDate],[EDI],[ttID],[pcdCutOffFlag],[ssoID] )
Select (Select Cust from wsct where sub =ZpSubCode) , ZPCustCode, ZPItemCode,ZPQty,WUSalesB.GetSalesManID (ZPSubCode), DocDate,'Y',
@InpDate, 'Y',dbo.GetTransType(ZPItemCode,DocDate,ZPQty, @InpDate), -- Get Transaction Type
dbo.GetCutOffFlag(ZPItemCode,DocDate,@InpDate),ssoID
From StgSO where (StatusID in (2002,2015)) AND (sflid = @sflid) AND (ssoID in (Select [id] from #ssoid)) AND --checked
(ZPCustCode in (Select HPCode from HP where HPCode = ZPCustCode)) AND
(ZPCustCode in (select HPCode from hp inner join ws on hp.HPPrv = ws.WSPrv where HPCode = ZpCustCode))

--INSERT SA PSO TABLE
Insert into PSO ([WSCode],[HPCode],[ITEM],[QTY],[SalesID],[OEDate],[CFM],[InpDate],[EDI],[ttID],[pcdCutOffFlag],[psoModUser]) --nasa psoModUser yung SSO ID
Select [WSCode],[HPCode],[ITEM],[QTY],[SalesID],[OEDate],[CFM],[InpDate],[EDI],[ttID],[pcdCutOffFlag], ssoID from #tempPSO --
set @s = @@ROWCOUNT
--- PSO Only
--- DB Only
Insert into #tempDB ([FrWS],[ToWS],[ITEM],[QTY], [SENDER],[DBDate],[InpDate],[EDI],[pcdCutOffFlag],[ssoID] )
Select (Select Cust from wsct where sub =ZpSubCode), ZPCustCode, ZPItemCode,ZPQty,WUSalesB.GetSalesManID (ZPSubCode), -- Get SalesMan ID
DocDate, @InpDate, 'Y', dbo.GetCutOffFlag(ZPItemCode,DocDate,@InpDate),ssoID
From StgSO where (StatusID in (2002,2015)) AND (sflid = @sflid) AND (ssoID in (Select [id] from #ssoid)) AND --checked
(ZPCustCode NOT in (Select HPCode from HP where HPCode = ZPCustCode)) AND
(ZPCustCode NOT in (select HPCode from hp inner join ws on hp.HPPrv = ws.WSPrv where HPCode = ZpCustCode))
--INSERT SA DB TABLE
Insert into DB ([FrWS],[ToWS],[ITEM],[QTY],[SENDER],[DBDate],[InpDate],[EDI],[pcdCutOffFlag],[dbModUser])
Select [FrWS],[ToWS],[ITEM],[QTY],[SENDER],[DBDate],[InpDate],[EDI],[pcdCutOffFlag],[ssoID] from #tempDB --nasa psoModUser yung SSO ID
set @s = @@ROWCOUNT
---DB Only

-- Update the Status of StgSO ID if Loaded
Update StgSO set StatusID = 2000 From #tempPSO where StgSO.ssoID = #tempPSO.ssoID
SET @RetProcessed = @@ROWCOUNT
Update StgSO set StatusID = 2000 From #tempDB where StgSO.ssoID = #tempDB.ssoID
SET @RetProcessed = @RetProcessed + @@ROWCOUNT
IF @@Error <> 0 -- Rollback2
BEGIN
--Update StgSO set StatusID = 2010 FROM #ssoid WHERE StgSo.ssoID = #ssoid.[ID]

update StgSo set statusID = 2010 from #ssoid where StgSo.ssoID = #ssoid.[ID]
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = ssoID where sflid = @sflid -- temporary set this to ssoID for linking purposes
set @s = @@ROWCOUNT
DELETE FROM PSO WHERE psoModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
DELETE FROM DB WHERE DB.dbModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
Update StgSO set ssoModUser = 'EDI' where sflid = @sflid-- bring back to Orig
DROP TABLE #tempPSO
DROP TABLE #tempDB
SET @RetVal = -1001 --uploading to StgSO to PSO/DB Table
RETURN
END

-- End Step 2
---- ***************************************************************************************
-- ( Step 3 )
-- ********************** update SourceFileLog *************************
select @totalCnt = count(sflid) from StgSO where sflid = @sflid and statusID <> 2008
select @totalProcessed = count(sflid) from StgSo where sflid = @sflid and statusID = 2000
if @totalCnt = @totalProcessed
BEGIN
update SourceFileLog set StatusID = 1000 where sflid = @sflid --completed
END
else
BEGIN
update SourceFileLog set StatusID = 1003 where sflid = @sflid --processed
END

---- ***************************************************************************************

IF @@Error <> 0 -- Rollback 3
BEGIN
update StgSo set statusID = 2010 from #ssoid where StgSo.ssoID = #ssoid.[ID]
set @s = @@ROWCOUNT
Update SourceFileLog set StatusID = 1003 where sflid = @sflid
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = ssoID where sflid = @sflid -- temporary set this to ssoID for linking purposes
set @s = @@ROWCOUNT
DELETE FROM PSO WHERE psoModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
DELETE FROM DB WHERE DB.dbModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = 'EDI' where sflid = @sflid-- bring back to Orig
DROP TABLE #ssoID
DROP TABLE #tempPSO
DROP TABLE #tempDB
SET @RetVal = -1002 --updating of SourceFileLog
RETURN
END

---- update stgSO.PSOID and DB.ID if insert is sucessful
Update StgSO set ssoModUser = ssoID where sflid = @sflid -- temporary set this to ssoID for linking purposes
set @s = @@ROWCOUNT
Update StgSO set PSOID = PSO.[ID] from PSO where StgSO.ssoModUser = PSO.psoModUser and sflid = @sflid
set @s = @@ROWCOUNT
Update StgSO set DBID = DB.[ID] from DB where StgSO.ssoModUser = DB.dbModUser and sflid = @sflid
set @s = @@ROWCOUNT
Update PSO set PSO.psoModUser = '' from StgSO where StgSO.ssoModUser = PSO.psoModUser and sflid = @sflid
set @s = @@ROWCOUNT
Update DB set DB.dbModUser = '' from StgSO where StgSO.ssoModUser = DB.dbModUser and sflid = @sflid
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = 'EDI' where sflid = @sflid-- bring back to Orig
set @s = @@ROWCOUNT
IF @@Error <> 0 -- Rollback 3
BEGIN
update StgSo set statusID = 2010 from #ssoid where StgSo.ssoID = #ssoid.[ID]
set @s = @@ROWCOUNT
Update SourceFileLog set StatusID = 1003 where sflid = @sflid
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = ssoID where sflid = @sflid -- temporary set this to ssoID for linking purposes
set @s = @@ROWCOUNT
DELETE FROM PSO WHERE psoModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
set @s = @@ROWCOUNT
DELETE FROM DB WHERE DB.dbModUser IN (SELECT ssoModUser from StgSO WHERE StgSO.sflid = @sflid )
set @s = @@ROWCOUNT
Update StgSO set ssoModUser = 'EDI' where sflid = @sflid-- bring back to Orig
set @s = @@ROWCOUNT
DROP TABLE #tempPSO
DROP TABLE #tempDB
SET @RetVal = -1003 -- updating of PSO/DB ID
RETURN
END
DROP TABLE #ssoID
DROP TABLE #tempPSO
DROP TABLE #tempDB
SET @RetVal = 0
RETURN
GO

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-05-26 : 22:53:59

Please format the code so we can atleast read it.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-26 : 23:40:41

how format do you want?

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede™

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-05-27 : 00:00:29

I can pretty much guarantee you that most people will not spend the time to read through your code. The reason is that it is not formatted properly, and is extremely hard to read.

If you want some help, you should spend the time to properly format the code so that it is not so much work (for us) to decipher.

-ec

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-27 : 07:15:11

At least use "code" tags (see faq), then whatever indenting you've used will be retained. I had the same experience as eyechart. I went as far as pasting your code into a QA window but gave up trying to read it. I hate reading weirdly formatted code.

Have you read BOL regarding Deadlocks? There is a large, helpfull section all about what causes them and how to avoid them.

Be One with the Optimizer
TG

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-28 : 10:33:51

You can review what is happening in Profiler - choose deadlock events to report on, and/or you can turn on trace flag 1204 (and I think there are one or two others that relate to deadlocks - see BOL) and review the instance errorlog.

Once you've narrowed down the objects involved in the deadlock, you will have a better starting point to track down the statements causing the deadlock(s).

Good luck - it really isn't that difficult.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-29 : 19:57:57

thnx.

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-05-30 : 00:41:20

quote:
Originally posted by raclede

thnx.
raclede

So, are you going to re-format the code or what?

-ec

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-30 : 00:42:14

we've turn on the trace, and hoping a deadlock will happen..

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-30 : 05:30:02

By the way:

I noticed that your procedure uses "IN" to determine existence

 AND SSOId in (Select [Id] From #SSOId)

Using "IN" like this is very expensive because an entire result set ("in list") must be generated to determine that there is one value that matches. A much better way to code this is:

 AND Exists (Select * From #SSOId TMP  Where SSOId = TMP.[Id])

What this does is return "true" (internally) on the very first match instead of generating a (possibly) massive internal "in list" which is then searched. Don't be concerned with the syntax " select * ". Some people insist on coding " select 1 " or something similar, but the optimizer knows that, in this case, it is doing an existence check and only "true" or "false" is generated internally.

ALSO: I see that you are not creating indexes on your temporary tables. While I really don't like #temp tables (there almost always is a better way to code your query), if you must use them, index them as you would a persisent table. If these are small tables, consider using table variables. Table variables can have a single-column primary key declared, but I have yet to prove that the declaration manifests itself into an index.

In large systems that use temp tables frequently, a bottleneck can occur on the tempdb allocation map pages that are an internal component to object management. (This is a documented bug.)

Without knowing anything about your system, I am going to predict that your deadlock(s) are going to be on the clustered indexe(s) on one of your smaller persistent tables. Generating indexes on your temp tables may help this, and adding an "order by" to the statements that populate the temp tables may also help - although if you take my advice and generate (clustered) indexes on these temp tables, you may not want to sort the data twice (the clustered index generation will sort the data by the key - at least if it is generated after the table is populated.) If the data is to be ordered by the key of the source table(s), an " Order By " may be inexpensive and, if you declare clustered primary keys or create clustered indexes on the temp tables before they are populated, sorts will be minimized (which also use tempdb and add to the allocation map problem stated above.

It's all a rich tapestry. :)

Good luck.

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-30 : 21:52:27

thnx alot.. Im going to modify my SP now.

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

yslash
Starting Member

1 Post

Posted - 2005-06-07 : 01:36:34

Is there any recommended method for formatting T-SQL nicely ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources