Posted by Tony Rogerson on 12/29/05 16:42

Hi,

Don't get distracted, the temporary table is not causing your deadlock,
rather the update on PL is.

Its feasible that your connection is locking index pages, data pages that
other connections also have locked before you grab them - you are updating
quite a lot of rows in one go so the transaction will be quite large.

Have you checked the query plan for this UPDATE? Index on some of the
columns in your WHERE clause will help reduce the IO.

READ UNCOMMITTED is redundant on the UPDATE - the locks will be placed
because you are updating the data, you could use READ UNCOMMITTED on ALL
your readers and that would help.

I don't tend to use such large composite keys like this, I would use a
surrogate - 'ID' integer column instead and update by joining using that (if
thats possible in your case).

Reading your end bit, if you are absolutely sure that nothing else is
accessing that table then the plan is probably deadlocking itself because of
parallelism which can happen, you can stop parallelism by using MAXDOP 1 on
the OPTIONS clause of the UPDATE statement.

Hope that helps.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


<fmatamoros@yahoo.com> wrote in message
news:1135823370.065365.186970@g44g2000cwa.googlegroups.com...
>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]