Posted by fmatamoros on 12/29/05 04:29

I sometimes get the following error from an update statement in a
stored procedure:

Transaction (Process ID 62) was deadlocked on thread | communication
buffer resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.

The isolation level is READ UNCOMMITTED and there are no explicit
transactions in the stored procedure. The update statement is as
follows:

UPDATE PL
SET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @comp
AND PL.PLAN_YEAR = @year
AND PL.FORECAST_QUARTER = @qtr
AND PL.VERSION_ID = @ver
AND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODE
AND PL.BUSINESS_UNIT_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.
It has the values that need to be set in the PL table. The PL table
has a clustered index on 8 columns. The filters (@comp, @year, ...)
select 230,088 rows. When the update succeeds it updates 43,692 rows
in about 15 seconds. Why does this sometimes deadlock and other times
succeed? There is nothing else running, so the process is deadlocking
on itself.

Thanks,
Frank

[Back to original message]