Re: Deadlock on Update using temp table

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@xxxxxxxxx> wrote in message
news:1135823370.065365.186970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>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)
  • Deadlock on Update using temp table
    ... transactions in the stored procedure. ... The update statement is as ... When the update succeeds it updates 43,692 rows ... Why does this sometimes deadlock and other times ...
    (comp.databases.ms-sqlserver)
  • Re: CLR Deadlock?
    ... It seems that when you disable/enable network connection, remoting opens new ... lot of problems with remoting as there is no timeout on TCP channel. ... That thread does not acquire any locks, so it should not deadlock. ...
    (microsoft.public.dotnet.framework.clr)
  • Re: Preventing deadlocks
    ... >> A deadlock does not kill a connection. ... The deadlock is occuring because one application ... > died out on the network and is holding its query open. ... There is a lock timeout and query timeout that you should be able to set ...
    (microsoft.public.sqlserver.server)
  • Granularity question
    ... Connection B gets exclusive lock on Resource 2 ... Deadlock occurs. ... For example, Connection A got share locks on rows/pages 1 and 2, then ...
    (microsoft.public.sqlserver)