473,883 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_Y EAR_AMOUNT = #tmpWorkPLPrior .PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @comp
AND PL.PLAN_YEAR = @year
AND PL.FORECAST_QUA RTER = @qtr
AND PL.VERSION_ID = @ver
AND PL.BUSINESS_UNI T_CODE = #tmpWorkPLPrior .BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior .PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior .BUDGET_CODE
AND PL.BUSINESS_UNI T_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrio r) 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

Dec 29 '05 #1
4 9624
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

Dec 29 '05 #2
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********@yah oo.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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_Y EAR_AMOUNT = #tmpWorkPLPrior .PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @comp
AND PL.PLAN_YEAR = @year
AND PL.FORECAST_QUA RTER = @qtr
AND PL.VERSION_ID = @ver
AND PL.BUSINESS_UNI T_CODE = #tmpWorkPLPrior .BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior .PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior .BUDGET_CODE
AND PL.BUSINESS_UNI T_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrio r) 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

Dec 29 '05 #3
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********@yah oo.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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_Y EAR_AMOUNT = #tmpWorkPLPrior .PRIOR_AMOUNT
FROM #tmpWorkPLPrior
WHERE PL.COMPANY = @comp
AND PL.PLAN_YEAR = @year
AND PL.FORECAST_QUA RTER = @qtr
AND PL.VERSION_ID = @ver
AND PL.BUSINESS_UNI T_CODE = #tmpWorkPLPrior .BUSINESS_UNIT
AND PL.PROJECT_ID = #tmpWorkPLPrior .PROJECT_ID
AND PL.BUDGET_CODE = #tmpWorkPLPrior .BUDGET_CODE
AND PL.BUSINESS_UNI T_CODE <> 'G7'

PL rows: 24,342,553
PL rows - Filtered: 230,088
#tmpWorkPLPrior rows: 3,641
Updated rows: 43,692

The temp table (#tmpWorkPLPrio r) 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

Dec 29 '05 #4
Using MAXDOP 1 on the OPTIONS clause seems to fix my deadlock problem.

Thanks,
Frank

Dec 30 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
6438
by: Anita | last post by:
Hi All, Can multiple updates on one table using single query generate deadlock ? For example, at the same time, there are 2 users run 2 queries as follows : User1 runs : update tab1 set tab1.v = tab1.v + 1 from tab1 inner join tab2 on tab1.no = tab2.no
7
9246
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
9
2933
by: Vorpal | last post by:
Here is a small sample of data from a table of about 500 rows (Using MSSqlserver 2000) EntryTime Speed Gross Net ------------------ ----- ----- 21:09:13.310 0 0 0 21:09:19.370 9000 NULL NULL 21:09:21.310 NULL 95 NULL 21:10:12.380 9000 NULL NULL 21:10:24.310 NULL 253 NULL
8
9557
by: Javauser | last post by:
Hi there we are getting the following db2 error on executeBatch() method that inserts n rows on a table (where n is between 50 and 200). SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001 errorCode : 911
15
10020
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried) 3-4 times and give up if after that it's still in deadlock. I'm very sure that many experienced people out there already deal with this issue somehow. Is there an alternative to it? Thanks for your comments and suggestions.
1
3694
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into "access_log", the referential integrity triggers generate these queries: SELECT 1 FROM ONLY "public"."application_type" x
2
1816
by: Hugo Flores | last post by:
Hi, I'm getting a deadlock on my database. Let me first tell you that this is a test database on a Win XP Professional. The SP where I'm getting the deadlock is this: PROCEDURE UpdateTestFields @id_Test int,
3
2200
by: shark | last post by:
Hi all. i am facing a deadlock problem .i have included the -t1204 and -T3605 trace flags and have got the following o/p pu tin sqls server logs. 2006-06-01 17:49:21.84 spid4 2006-06-01 17:49:21.84 spid4 Wait-for graph 2006-06-01 17:49:21.84 spid4 2006-06-01 17:49:21.84 spid4 ... 2006-06-01 17:49:26.92 spid4 ResType:LockOwner Stype:'OR' Mode: S
0
1964
by: halex | last post by:
Hello, I am having deadlock problem when I have a lot of visitors on my website at the same time. I am using NetTiers templates to generate C# classes for accessing DB layer and problem is in my custom Store Procedure. I have Article table and ArticleLanguage table. One record from Article (Id, Position, StatusId) table is the same for all languages and in ArticleLanguage (Id, LanguageId, ArticleId, Name) table I have only article names...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9789
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11129
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10408
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9569
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7967
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.