Deadlock on Update using temp table

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

.

Relevant Pages

  • Re: Deadlock on Update using temp table
    ... The update statement is as ... > #tmpWorkPLPrior rows: 3,641 ... When the update succeeds it updates 43,692 rows ... Why does this sometimes deadlock and other times ...
    (comp.databases.ms-sqlserver)
  • RE: TDS_RET_STATUS exception
    ... "Kev" wrote: ... high enough the occasional deadlock can occur. ... However, what I'm currently getting is that the first retry attempt fails, ... found for the stored procedure output parameters. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: TDS_RET_STATUS exception
    ... high enough the occasional deadlock can occur. ... However, what I'm currently getting is that the first retry attempt fails, ... found for the stored procedure output parameters. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • TDS_RET_STATUS exception
    ... high enough the occasional deadlock can occur. ... This is ok as the java code that executed the deadlocked CallableStatement ... However, what I'm currently getting is that the first retry attempt fails, ... found for the stored procedure output parameters. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • RE: TDS_RET_STATUS exception
    ... JDBC driver with Java 1.5.0 06. ... high enough the occasional deadlock can occur. ... However, what I'm currently getting is that the first retry attempt fails, ... found for the stored procedure output parameters. ...
    (microsoft.public.sqlserver.jdbcdriver)