|
|
Posted by John Bell on 12/29/05 13:15
Hi
You don't give the version of SQL Server you are running?
In general it is better to create the temporary table separately.
Heavy use of tempdb may be helped by moving it to it's own drive(s) and
using multiple files see
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
Also check out http://support.microsoft.com/kb/271509/EN-US/ and
http://support.microsoft.com/kb/224453/EN-US/ on how to identify blocking.
John
<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
>
Navigation:
[Reply to this message]
|