Subquery Causing Deadlocks?

[fuzzyocelot]

Is it possible that the code below is causing deadlocks because of how it's written? Meaning, the table being updated is also referenced in a subquery within the update statement. We know deadlocks are occurring on the tables and this stored procedure was captured in an SQL profiler trace. Would rewriting the procedure help? (BTW, I did not write this proc.)

The code below is from a stored procedure running in SQL Server 2005 SP2.

Declare @intALSRcdId int, @intUserRcdId int 
set @intALSRcdId =1
set @intUserRcdId=20

UPDATE tblPSFS 
SET decPaidAmt=ISNULL(psfs.decInitPdAmt,0.00)+t.PaidAmt,
    decQ1PaidAmt=Q1PaidAmt, intLastUserRcdId = @intUserRcdId	
FROM tblPSFS psfs 
JOIN 
 (SELECT psfsal.intPSFSRcdId,
         'PaidAmt'=ISNULL(SUM(psfsal.decPaidAmt),0.00),
         'Q1PaidAmt'=ISNULL(SUM(CASE psfsal.inyCQuarter WHEN 1 THEN ISNULL(psfsal.decPaidAmt,0.00) ELSE 0.00 END),0.00)
  FROM    dbo.tblPSFSAL psfsal
  WHERE   psfsal.intPSFSRcdId in 
	(select psfs.intPSFSRcdId
	 from tblPSFS psfs
	 where coalesce(psfs.intPSSCRcdID,psfs.intPSLICRcdID) in
					(select coalesce(als.intPSSCRcdID,als.intPSLICRcdID) 
					from tblALS als
					where als.intALSRcdId=ISNULL(@intALSRcdId,als.intALSRcdId)						)
	) 
    GROUP BY psfsal.intPSFSRcdId ) as t
ON t.intPSFSRcdId=psfs.intPSFSRcdId

UPDATE tblFS
SET   decPaidAmt=t.PaidAmt+ISNULL(fs.decInitPdAmt,0.00),
      decQ1PaidAmt=t.Q1PaidAmt,
	  intLastUserRcdId = @intUserRcdId	
FROM tblFSfs JOIN 
     (SELECT psfs.intFSRcdId,
             'PaidAmt'=SUM(ISNULL(psfsal.decPaidAmt,0.00)),
             'Q1PaidAmt'=ISNULL(SUM(CASE psfsal.inyFSQuarter WHEN 1 THEN ISNULL(psfsal.decPaidAmt,0.00) ELSE 0.00 END),0.00)
      FROM    dbo.tblPSFSAL psfsal JOIN 
              dbo.tblPSFS psfs ON psfsal.intPSFSRcdId=psfs.intPSFSRcdId
      WHERE   psfs.intFSRcdId in 
				(select intFSRcdId from tblPSFS psfs where coalesce(psfs.intPSSCRcdID,psfs.intPSLICRcdID)in
					(select coalesce(als.intPSSCRcdID,als.intPSLICRcdID) 
						from tblALS als where als.intALSRcdId=ISNULL(@intALSRcdId,als.intALSRcdId)))
      GROUP BY psfs.intFSRcdId) as t
    ON t.intFSRcdId=fs.intFSRcdId

Thanks!

 

[gmmastros]

Deadlock conditions are likely to be worse when queries run slow. As such, I suggest you try to make the updates faster. This can be tricky business. If this were happening on my database, I would first see which (of the two) updates is causing the problem. I would begin with timing the execution. Like this:

Declare @Start DateTime
Set @Start = GetDate()

-- Your first update here

Select DateDiff(Millisecond, @Start, GetDate())
Set @Start = GetDate()

-- Your second update here

Select DateDiff(Millisecond, @Start, GetDate())

If one of the updates is fast and the other is slow, then that is where you should focus your attention. If both updates are equally slow, then you'll need to re-work both of them.

I would start by trying to re-write the queries so they don't have an [!]in[/!] statement in the where clause. Try writing this as a proper join to see if you can speed it up.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fuzzyocelot]

Yes, it makes sense to me. I'll give it a shot. Thanks!