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

SQL Server Development (2000)

deadlock

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-05-11 : 13:59:09

Folks:

I need help with a Deadlock issue I am facing. I tried a lot and cannot fix this. Any help on this is highly appreciated. Version SQL 2000 SP4.
The table name where the Deadlock is occuring is 'tblholdings'. Below are the 2 Stored Procedures, Deadlock information from Error Log and the indexes on the table information.

*****************************************************************
STORED PROCEDURE # 1
*****************************************************************

CREATE PROCEDURE [dbo].[spQOpsRptDateAdjFactor]
(
@AsOf DATETIME
)

AS

DECLARE @localAsOf DATETIME
SET @localAsOf = @AsOf

-- Table to hold results
DECLARE @tblResults TABLE
(
Security VARCHAR(50),
SecID int,
Factor FLOAT,
FactorDate DATETIME
)

-- Get holdings
INSERT INTO @tblResults
SELECT DISTINCT
HL.Security,
HL.SecurityID,
-- Analysis.dbo.fnGenUseGetSDSVal(@localAsOf, HL.SecurityID, 14) AS CUSIP,
HL.Factor,
HL.FactorDate
FROM pmWhse..tblHoldings HL
WHERE
HL.AsOf >= dateadd(day, -60, @localAsOf) AND
HL.Factor > 0 AND
left(HL.Security, 1) <> '|'

select Analysis.dbo.fnGenUseGetSDSVal(@localAsOf, SecID, 14) as CUSIP
from @tblResults
where Analysis.dbo.fnGenUseGetSDSVal(@localAsOf, SecID, 527) in ('ABS ADJ ACT/360', '*CMO FLOAT ACT', 'ABS Act/360P12U', 'CMO Act/360P12U')
order by
CUSIP

*****************************************************************
STORED PROCEDURE # 2
*****************************************************************

CREATE PROCEDURE spUpdateKRD (@asofdate DATETIME)
AS

DECLARE @localasofdate DATETIME

SET @localasofdate = @asofdate

DECLARE @propertydate TABLE (SecID INT, PropertyID INT, AsOfDate DATETIME, PropertyValue FLOAT
PRIMARY KEY CLUSTERED (SecID, PropertyID))

INSERT @propertydate
SELECT
H.SecurityID,
PM.PropertyID,
MAX(SDF.AsOfDate),
NULL
FROM pmWhse..tblHoldings H
CROSS JOIN Analysis..tblPropertyMaster PM
LEFT OUTER JOIN Analysis..tblSecDataFlt SDF ON SDF.SecID = H.SecurityID AND SDF.PropertyID = PM.PropertyID AND SDF.AsOfDate <= @localasofdate
WHERE
PM.PropertyID IN(
509,--Key6MoMove
510,--Key1YrMove
511,--Key2YrMove
512,--Key3YrMove
513,--Key5YrMove
514,--Key10YrMove
515,--Key20YrMove
516,--Key30rMove
394,--MultiplierUp
414 --MultiplierDown
)
AND H.AsOf = @localasofdate
GROUP BY H.SecurityID, PM.PropertyID

DELETE FROM @propertydate
WHERE
AsOfDate IS NULL
AND PropertyID NOT IN (394, 414)

UPDATE
PD
SET PD.PropertyValue = SDF.PropertyValue
FROM @propertydate PD
INNER JOIN Analysis..tblSecDataFlt SDF
ON
SDF.AsOfDate = PD.AsOfDate
AND SDF.SecID = PD.SecID
AND SDF.PropertyID = PD.PropertyID
AND PD.AsOfDate IS NOT NULL

--Set the 30 Yr key rate property = 20 Yr + 30 Yr

UPDATE
PD1
SET PD1.PropertyValue = ISNULL(PD1.PropertyValue, 0) + ISNULL(PD2.PropertyValue, 0)
FROM
@propertydate PD1
LEFT OUTER JOIN @propertydate PD2 ON PD2.SecID = PD1.SecID AND PD2.PropertyID = 515
WHERE
PD1.PropertyID = 516

--Average the mulitipliers

UPDATE
PD1
SET PD1.PropertyValue = (
ISNULL(ISNULL(PD1.PropertyValue, PD2.PropertyValue), 1) +
ISNULL(ISNULL(PD2.PropertyValue, PD1.PropertyValue), 1)) / 2
FROM
@propertydate PD1
LEFT OUTER JOIN @propertydate PD2 ON PD2.SecID = PD1.SecID AND PD2.PropertyID = 414
WHERE
PD1.PropertyID = 394

DELETE FROM @propertydate WHERE PropertyID IN (414, 515)

--Multiply the key rates by the multiplier

UPDATE
PD1
SET PD1.PropertyValue = PD1.PropertyValue * PD2.PropertyValue
FROM
@propertydate PD1
LEFT OUTER JOIN @propertydate PD2 ON PD2.SecID = PD1.SecID AND PD2.PropertyID = 394
WHERE
PD1.PropertyID != 394

DELETE FROM @propertydate WHERE PropertyID = 394

--***************************************tblWhseHlds*****************************************

--KeySensDate

UPDATE
WH
SET
KeySensDate =
(
SELECT ISNULL(MAX(PD1.AsOfDate),'1/1/1900')
FROM
@propertydate PD1
WHERE
PD1.SecID = WH.SecurityID
AND PD1.PropertyID IN (509,510,511,512,513,514,515,516)
)
FROM
pmWhse..tblWhseHlds WH
WHERE
AsOf = @localasofdate

--Key6MoMove

UPDATE
pmWhse..tblWhseHlds
SET Key6MoMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 509
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key1YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key1YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 510
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key2YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key2YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 511
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key3YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key3YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 512
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key5YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key5YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 513
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key10YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key10YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 514
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--Key30YrMove

UPDATE
pmWhse..tblWhseHlds
SET Key30YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblWhseHlds.CFace * pmWhse..tblWhseHlds.QtyUnitFactor) * (pmWhse..tblWhseHlds.PriceUnitFactor * pmWhse..tblWhseHlds.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblWhseHlds.SecurityID
AND PD1.PropertyID = 516
AND pmWhse..tblWhseHlds.AsOf = @localasofdate

--***************************************tblHoldings*****************************************

--KeySensDate

UPDATE
H
SET
KeySensDate =
(
SELECT ISNULL(MAX(PD1.AsOfDate),'1/1/1900')
FROM
@propertydate PD1
WHERE
PD1.SecID = H.SecurityID
AND PD1.PropertyID IN (509,510,511,512,513,514,515,516)
)
FROM
pmWhse..tblHoldings H
WHERE
AsOf = @localasofdate

--Key6MoMove

UPDATE
pmWhse..tblHoldings
SET Key6MoMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 509
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key1YrMove

UPDATE
pmWhse..tblHoldings
SET Key1YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 510
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key2YrMove

UPDATE
pmWhse..tblHoldings
SET Key2YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 511
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key3YrMove

UPDATE
pmWhse..tblHoldings
SET Key3YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 512
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key5YrMove

UPDATE
pmWhse..tblHoldings
SET Key5YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 513
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key10YrMove

UPDATE
pmWhse..tblHoldings
SET Key10YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 514
AND pmWhse..tblHoldings.AsOf = @localasofdate

--Key30YrMove

UPDATE
pmWhse..tblHoldings
SET Key30YrMove =
PD1.PropertyValue * .01 * (pmWhse..tblHoldings.CFace * pmWhse..tblHoldings.QtyUnitFactor) * (pmWhse..tblHoldings.PriceUnitFactor * pmWhse..tblHoldings.Price)
FROM
@propertydate PD1
WHERE
PD1.SecID = pmWhse..tblHoldings.SecurityID
AND PD1.PropertyID = 516
AND pmWhse..tblHoldings.AsOf = @localasofdate

*****************************************************************
DEADLOCK INFORMATION FROM ERRORLOG
*****************************************************************

2007-05-07 20:04:56.20 spid1
Deadlock encountered .... Printing deadlock information
2007-05-07 20:04:56.20 spid1
2007-05-07 20:04:56.20 spid1 Wait-for graph
2007-05-07 20:04:56.20 spid1
2007-05-07 20:04:56.20 spid1 Node:1
2007-05-07 20:04:56.20 spid1 PAG: 8:1:1807867 CleanCnt:2 Mode: SIU Flags: 0x2
2007-05-07 20:04:56.20 spid1 Grant List 0::
2007-05-07 20:04:56.20 spid1 Owner:0x7074efc0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:81 ECID:0
2007-05-07 20:04:56.20 spid1 SPID: 81 ECID: 0 Statement Type: INSERT Line #: 52
2007-05-07 20:04:56.20 spid1 Input Buf: Language Event: EXEC spQOpsRptDateAdjFactor '05/04/2007'
2007-05-07 20:04:56.20 spid1 Grant List 1::
2007-05-07 20:04:56.20 spid1 Requested By:
2007-05-07 20:04:56.20 spid1 ResType:LockOwner Stype:'OR' Mode: IX SPID:77 ECID:0 Ec:(0x5A4F1508) Value:0x4807a4a0 Cost:(0/1F4988)
2007-05-07 20:04:56.20 spid1
2007-05-07 20:04:56.20 spid1 Node:2
2007-05-07 20:04:56.20 spid1 PAG: 8:1:1807868 CleanCnt:2 Mode: IX Flags: 0x2
2007-05-07 20:04:56.20 spid1 Grant List 1::
2007-05-07 20:04:56.20 spid1 Owner:0x1ac946c0 Mode: IX Flg:0x0 Ref:4 Life:02000000 SPID:77 ECID:0
2007-05-07 20:04:56.20 spid1 SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 231
2007-05-07 20:04:56.20 spid1 Input Buf: Language Event: EXEC pmWhse..spUpdateKRD '05/04/07'

2007-05-07 20:04:56.20 spid1 Requested By:
2007-05-07 20:04:56.20 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:81 ECID:0 Ec:(0x6C8BD370) Value:0x23ee8820 Cost:(0/0)
2007-05-07 20:04:56.20 spid1 Victim Resource Owner:
2007-05-07 20:04:56.20 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:81 ECID:0 Ec:(0x6C8BD370) Value:0x23ee8820 Cost:(0/0)

********************************************************************

The tblholding has the following INDEXES:

IX_tblHoldings_1 nonclustered located on PRIMARY (AsOf, Portfolio, Security)
IX_tblHoldings_2 nonclustered located on PRIMARY (GenId, Security, Portfolio)
IX_tblHoldings_3 nonclustered located on PRIMARY (Security, Portfolio, AsOf)
PK_tblHoldings clustered, unique, primary key located on PRIMARY (AsOf, PortfolioId, SecurityId, Coupon, MaturityDate, LotJagged)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 14:25:41

you deadlock occurs when you try to insert into a table variable in the 1st proc
data that the update in the 2nd has locked.
and the update in the 2nd proc wants to update data locked by the insert in the 1st proc

can you provide more info?
how fast must this be executed?
how large are the tables?

look into lock hints. you can specify tablock that locks the entire table during the operation.
but this is usually the last resort.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-05-11 : 14:36:40

Stored Proc: spQOpsRptDateAdjFactor is a scheduled and runs at 8:15 PM. and takes around 5 mins to complete.

Stored Proc: spUpdateKRD is a not scheduled but is part of a batch job and can run anytime between 8:00 and 8:45 PM. and takes around 15-20 mins to complete.

Table 'tblHoldings' size:

Rows: 3763337
Reserved: 13769264
Data: 6535864
Index Size: 1829256
Unused: 5404144

Thanks for all your help !

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 14:43:16

well then i'd suggest that you schedule the spQOpsRptDateAdjFactor after or before the batch job
depending on your business requirements.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sqldba20
Posting Yak Master

183 Posts

Posted - 2007-05-11 : 14:45:45

Well.. I cannot change the timings...I thought about the timings but the business partners don't agree...Is there any other alternative ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 14:50:34

try using tablockx table hint.

insert into ...
select ...
from yourTable
WITH (TABLOCK)
where ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources