Deadlock on Update using temp table

[fmatamoros]

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

[DIRVOEJERAUS]

Hi Frank

I don't have the skills and time to analyse your specific issue, but I
hope this general link could help you:
SQL Server technical bulletin - How to resolve a deadlock:
http://support.microsoft.com/kb/832524/en-us

Cheers
SMF

[John Bell]

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...b;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

<fm********@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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

[Tony Rogerson]

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
<fm********@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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

[fmatamoros]

Using MAXDOP 1 on the OPTIONS clause seems to fix my deadlock problem.

Thanks,
Frank